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

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.

-----Original Message-----
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
.

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message ...
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

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)

wrote in message
...
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

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.
--
Regards,
Tom Ogilvy

"chris" wrote in message
...
try this:
Rows(ActiveSheet.UsedRange.Rows.Count).Row

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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tom Ogilvy" wrote in message ...
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.
--
Regards,
Tom Ogilvy

"chris" wrote in message
...
try this:
Rows(ActiveSheet.UsedRange.Rows.Count).Row

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
--
Regards
Tom Ogilv

"chris" wrote in messag
..
try this
Rows(ActiveSheet.UsedRange.Rows.Count).Ro

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



chris wrote:

Then i guess this will do the trick!

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

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

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.
--
Regards,
Tom Ogilvy

"chris" wrote in message
...
try this:
Rows(ActiveSheet.UsedRange.Rows.Count).Row




--

Dave Peterson