Help Finding the Final Row

[Guest]

I need to set a variable equal to my final row that
contains data. I have a function, but it only checks for
the final row that contains data in column A. My final
row sometimes doesn't contain data in column A, but it
will always be in column A or column B. This is what I
have. Thanks for the help. I need it to give the number
of the last row containing data in column A or column B/

FinalRow = Cells(65536, 1).End(xlUp).Row

 

[Kris]

I use this:

lastrow = ActiveSheet.UsedRange.Rows.Count

Likewise, you can use the following to find the last
column:

lastcolumn = ActiveSheet.UsedRange.Columns.Count

Keep in mind that if there are formats in cells that it
will count those as well, so this may or may not be
exactly what you need if you just want the last row with
data.

 

[Ron de Bruin]

You can use this example that use a function

Sub test()
MsgBox LastRow(ActiveSheet)
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[Bob Phillips]

FinalRowA = Cells(Rows.Count, "A").End(xlUp).Row
FinalRowB = Cells(Rows.Count, "B").End(xlUp).Row
FinalRow = IIf(FinalRowA > FinalRowB, FinalRowA, FinalRowB)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

goto a blank sheet.

enter data in A11, A12, A13

Now run you code:

? Rows(ActiveSheet.UsedRange.Rows.Count).Row
3

Doesn't quite give the correct answer.

You can't assume the usedrange always starts with the first row.

 

[Ron de Bruin]

This will work then

Sub test()
Dim Lastrow As Long
With ActiveSheet
Lastrow = .UsedRange.Rows.Count + .UsedRange.Cells(1).Row - 1
End With
MsgBox Lastrow
End Sub

But if you format cells below your data this cells are also included in
the usedrange.
So if you want to know the last row with data don't use Usedrange but this

Sub test()
MsgBox LastRow(ActiveSheet)
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[Guest]

Then i guess this will do the trick

RwCnt = ActiveSheet.UsedRange.Rows.Coun
LastRow = ActiveSheet.UsedRange.Offset(x).Resize(1).Row - 1

----- Tom Ogilvy wrote: ----

goto a blank sheet

enter data in A11, A12, A1

Now run you code

? Rows(ActiveSheet.UsedRange.Rows.Count).Ro


Doesn't quite give the correct answer

You can't assume the usedrange always starts with the first row

 

[Dave Peterson]

Sometimes setting a range to the .usedrange will reset the lastusedcell to what
you think it should be:

Option Explicit
Sub testme()

Dim DummyRng As Range

With Worksheets("sheet1")
Set DummyRng = .UsedRange
MsgBox .UsedRange.Rows(.UsedRange.Rows.Count).Row
End With

End Sub