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.
--
rpw

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" wrote:

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


"rpw" wrote:

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

--
rpw


"BobT" wrote:

> 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" wrote:
>
> > 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.
> > --
> > rpw

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 wrote:
>
> 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
>
> "rpw" wrote:
>
> > 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.
> > --
> > rpw

--

Dave Peterson

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


"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
> >
> > "rpw" wrote:
> >
> > > 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.
> > > --
> > > rpw
>
> --
>
> Dave Peterson
>