Built in Column Length Function?

[shrubble]

When I'm writing a VBA module in Excel, is this a built in function to determins the beginning and end of a column, or do I need to write an iterator to probe for column contents?

EX: If I have a column that is only 10 rows deep, is there something that will return that number?

Thanks in advance!

deletion mistake
no I can't recover that
you didn't save it

-Shrubble

http://www.ishrubble.com

 

[brucegn]

There are a couple of ways to do what you want, these are just a few

Dim rngCnt As Integer
Columns("C:C").Select 'select the column of data
Selection.CurrentRegion.Select 'select all rows that hold data
rngCnt = Selection.CurrentRegion.Cells.Count 'gives you the count of rows in current region

'will also give you the count of all cells in data column
Range("C1", Range("C1").End(xlDown)).Count

When using End() the xlDown will will select the last cell that holds data, provided there are no blanks.

As I said there are other ways, and if these do not meet your needs please post back.

 

[PHV]

Take a look in the FAQ area of this forum.
At least 2 faqs are talking about the last row.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Shrubble,

Function LastRowInColumn(rng As Range) As Long
  Application.Volatile
  With rng
    If .Parent.Cells(.Parent.Cells.Rows.Count, .Column).Value = "" Then
      LastRowInColumn = .Parent.Cells(.Parent.Cells.Rows.Count, .Column).End(xlUp).Row
    Else
      LastRowInColumn = .Parent.Cells(.Parent.Cells.Rows.Count, .Column).Row
    End If
  End With
End Function
Function FirstRowInColumn(rng As Range) As Long
  Application.Volatile
  With rng
    If .Parent.Cells(1, .Column).Value = "" Then
      FirstRowInColumn = .Parent.Cells(1, .Column).End(xlDown).Row
    Else
      FirstRowInColumn = .Parent.Cells(1, .Column).Row
    End If
  End With
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[shrubble]

Thanks folks!

deletion mistake
no I can't recover that
you didn't save it

-Shrubble

http://www.ishrubble.com

 

[ETID]

All columns starting at row 1?
then try this...


Sub Column_Range()

aca = ActiveCell.Address
Columns("D").Select
Selection.End(xlDown).Select

Do Until lr = Selection.Row
If Selection.Row = 65536 Then
Exit Do
Else
lr = Selection.Row
Selection.End(xlDown).Select
End If
Loop

Range(aca).Select
MsgBox ("last row = " + CStr(lr))


End Sub

 

[ETID]

...never mind