My apologies - I misunderstood you have formulas in column A that either
display a value or display a blank. The VBA code I gave you would give the
last cell that contains a formula regardless of whether or not it displayed a
value or blank. I think you wanted the last cell that displays a value. If
so, the lookup formula should still work, but you could change it to
=IF(A65536="",LOOKUP(2, 1/(A1:A65535<""),ROW(A1:A65535)),ROW(A65536))
if you need to check the last cell on the sheet. In VBA, try:
Sub test()
Dim rngLast As Range
With Worksheets("Sheet1")
Set rngLast = .Range("A:A").Find( _
what:="*", _
after:=.Range("A1"), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByColumns, _
searchdirection:=xlPrevious, _
MatchCase:=False, _
matchbyte:=False)
End With
If Not rngLast Is Nothing Then
MsgBox rngLast.Row
End If
End Sub
"JMB" wrote:
you could use a formula (since you posted in the worksheet function group)
=LOOKUP(2, 1/(A1:A65535<""),ROW(A1:A65535))
assuming there will never be data in row 65536 (XL versions prior to 2007
don't accept entire columns as array arguments).
Probably easier to use VBA, which is more likely what you're after:
Sub test()
Dim lngLastRow As Long
With Worksheets("Sheet1")
If IsEmpty(.Cells(.Rows.Count, 1)) Then
lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Else: lngLastRow = .Rows.Count
End If
End With
MsgBox lngLastRow
End Sub
"ub" wrote:
Hi
I have formula in column 'A' from 'A1:A100'. But only approx 26 cells have
values.
I want to know the last row # for column 'A' that has value. There is no
empty row in between 2 rows.
Actually I want to setup print command in my VB code to print only the rows
that have value.
If there is another way of doing this then please advise
Thanks