How do I identify range of filtered sheet?

[rpw]

Hello all,

Using Excel 2007. I have a worksheet that I filter and then copy/paste the
results to another worksheet. I have a list of filter criteria and perform a
new filter for each item in the list.

When I apply the first filter, the result is 9 rows - A3:A86. The next
filter I apply will have different results - 5 rows - A17:A345.

My 'stuck point' is programatically identifying the results range
(specifically the row numbers) after the filter is applied. Row 1 is column
headers.

I hope that this is clear enough. Any help is greatly appreciated. Thanks
in advance.

 

[BobT]

Not too hard.

1. Select Range("A1") (gets you to the top of your data and headers)
2. Selection.End(xlDown).Select (this gets you to the bottom of the list)
3. MyLastRow = Activecell.row
4. Selection.End(xlToRight).Select (this gets you to the last column with
text in it - assuming all columns are filled).
5. MyLastCol = Activecell.column

Now you know your range. Of course, you may want to test your row to see if
it is 65536 - which would indicate that your filters returned zero rows.

 

[rpw]

Cancel this question. Thank you all because I've been able to (finally) find
what I needed from other posts. Here's shortcut version in case anyone is
interested:

Dim ws1 As Worksheet
Dim LastRow As Long, FirstRow As Long

Set ws1 = Worksheets("MySheet")

ws1.Activate
With ws1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
FirstRow = Cells.SpecialCells(xlCellTypeVisible).Areas(2).Row
End With
MsgBox FirstRow
MsgBox LastRow

 

[rpw]

Hi BobT,

Thanks for the quick response.

Not to sound ungrateful, but I probably wasn't clear enough in my
description and your suggestion isn't quite what I wanted. I guess I was
trying to convey that I was having difficulting finding the row number of the
second row (first visible row below the header (row1)). Each successive
filter would have a different second row number.

I guess I got frustrated searching for the solution here too soon and posted
the question. Shortly thereafter I was able to find other posts that
provided enough to come up with a suitable solution.

Thanks again for your help and time and sorry to have bothered you.

 

[Dave Peterson]

Be careful with that firstrow line. Depending on where the visible rows are,
you may not get what you want.

Imagine that row 1 contains the headers and rows 2-99 contain the same value.
When you filter by that value, the first row will be row 2, not the first row of
the second area--heck, there may not even be a second area!

I usually use something like:

With activesheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "only the headers are visible"
else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

 

[rpw]

Thanks for the helpful advice and solution. I hadn't considered the "only
headers visible' possibility.