Find last used row in a column (not in the whole worksheet)

[myemail.an]

Hi all,

I was wondering if you can help me with this:

I need to:
1) identify the last used row in a column (not in the whole worksheet)
2) identify whether a column is blank or not

The two are of course related - if I figure out for the 1st point,
then the other is straightforward.

Any suggestions?

Thanks in advance!

PS I use Excel 2007

 

[Dave Peterson]

dim myCol as long
dim LastRow as long
mycol = 123 'an example

lastrow = -1 'never happen in excel!
with activesheet
if application.counta(.cells(1,mycol).entirecolumn) = 0 then
msgbox "the column is empty"
else
lastrow = .cells(.rows.count,mycol).end(xlup).row
msgbox lastrow
end if
end with

 

[myemail.an]

Thanks! In the meanwhile, I came up with this:

Function FindLastRowInColumn(MyColumn As Integer, _
MyWorksheet As Worksheet)

'MyColumn: Column being searched
'MyWorksheet: Worksheet being searched

'Output: last used row; if 0 ==> blank column

Dim MyRange As Range

Set MyRange = MyWorksheet.Cells(1,
MyColumn).EntireColumn.Find("*", _
SearchDirection:=xlPrevious)

If MyRange Is Nothing Then
FindLastRowInColumn = 0
Else
FindLastRowInColumn = MyRange.Row
End If


End Function