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

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

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