Lost of threads here mentioned about how to get the last used row but I
couldnt find one about the last used row within a filtered range?
Say my used rows are from A1 to A20, row A20 may or may not show up after
filter, what is the code to get the 20 as variable?
Any idea please?
Regards

Try

Application.Intersect(Activesheet.Usedrange,Column s(1)).Rows.Count

--
Jacob


"Seeker" wrote:

Lost of threads here mentioned about how to get the last used row but I
couldnt find one about the last used row within a filtered range?
Say my used rows are from A1 to A20, row A20 may or may not show up after
filter, what is the code to get the 20 as variable?
Any idea please?
Regards

Jacob's code returns the number of rows in the UsedRange. This will equate to
the last row of the AutoFilter range if the AutoFilter range starts from row
1. If the AutoFilter is set up with a number of rows at the top of the screen
before the column headers then Jacob's code does not return the worksheet row
number of the last row of AutoFilter. (I often set up AutoFilter with a
number of rows at the top of the screen and I Freeze Panes from under the
column headers so that area of the screen is always visible and I place
various subtotal functions in the frozen area.)

Anyway the following returns the actual worksheet row number of the last row
of AutoFilter. (Not the last visible row; last row of AutoFiltered Range.)

With Sheets("Sheet1").AutoFilter.Range
lastRow = .Rows(.Rows.Count).Row
End With

The following can be used for last visible row but it is unreliable if data
has been added below the Autofilter range and then deleted. (Saving the file
seems to fix this but see the next example that I have found more reliable.)

With Sheets("Sheet1").AutoFilter.Range
lastVisibleRow = .SpecialCells(xlLastCell).Row
End With

Following method seems more reliable for last visible row. It only looks
within the Autofiltered range and including SearchDirection:=xlPrevious and
After:=.Cells(1, 1), forces it to start looking at the last cell in the last
row of the range. Note that SearchOrder:=xlByRows is also important.

With Sheets("Sheet1").AutoFilter.Range
lastVisibleRow = .Cells _
.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row
End With


--
Regards,

OssieMac

Hi Jacob,
Thanks for your help and sorry for late reply. I have problem as follow.
Let say I have data in A1 to A20 and first run of below code can place 20 in
cell A21. I pressed delete button or use ClearContents to clean up A21, next
run cursor stays in A22 and indicate 21 but I need the result always stay in
row A21 indicate 20.
Regards

LUsedRow = Application.Intersect(ActiveSheet.UsedRange,
Columns(1)).Rows.Count
Range("A" & LUsedRow + 1) = LUsedRow


"Jacob Skaria" wrote:

Try

Application.Intersect(Activesheet.Usedrange,Column s(1)).Rows.Count

--
Jacob


"Seeker" wrote:

Lost of threads here mentioned about how to get the last used row but I
couldnt find one about the last used row within a filtered range?
Say my used rows are from A1 to A20, row A20 may or may not show up after
filter, what is the code to get the 20 as variable?
Any idea please?
Regards

Hi Jacob,
Your code works but I need to have more arrangement in my code before I can
adopt it, so to save the hustle, I use OssieMacs method this time. Anyway,
thanks again for your help.
Regards


"Jacob Skaria" wrote:

Try

Application.Intersect(Activesheet.Usedrange,Column s(1)).Rows.Count

--
Jacob


"Seeker" wrote:

Lost of threads here mentioned about how to get the last used row but I
couldnt find one about the last used row within a filtered range?
Say my used rows are from A1 to A20, row A20 may or may not show up after
filter, what is the code to get the 20 as variable?
Any idea please?
Regards

Hi OssieMac,
Thanks for your explanation. I will take your advice and place my total
amount above the autofilter column headers instead at the bottom of filtered
data.
Regards


"OssieMac" wrote:

Jacob's code returns the number of rows in the UsedRange. This will equate to
the last row of the AutoFilter range if the AutoFilter range starts from row
1. If the AutoFilter is set up with a number of rows at the top of the screen
before the column headers then Jacob's code does not return the worksheet row
number of the last row of AutoFilter. (I often set up AutoFilter with a
number of rows at the top of the screen and I Freeze Panes from under the
column headers so that area of the screen is always visible and I place
various subtotal functions in the frozen area.)

Anyway the following returns the actual worksheet row number of the last row
of AutoFilter. (Not the last visible row; last row of AutoFiltered Range.)

With Sheets("Sheet1").AutoFilter.Range
lastRow = .Rows(.Rows.Count).Row
End With

The following can be used for last visible row but it is unreliable if data
has been added below the Autofilter range and then deleted. (Saving the file
seems to fix this but see the next example that I have found more reliable.)

With Sheets("Sheet1").AutoFilter.Range
lastVisibleRow = .SpecialCells(xlLastCell).Row
End With

Following method seems more reliable for last visible row. It only looks
within the Autofiltered range and including SearchDirection:=xlPrevious and
After:=.Cells(1, 1), forces it to start looking at the last cell in the last
row of the range. Note that SearchOrder:=xlByRows is also important.

With Sheets("Sheet1").AutoFilter.Range
lastVisibleRow = .Cells _
.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row
End With


--
Regards,

OssieMac