How to have VBA select only visible results of Adv Filter

[sagain2k]

I have a file that contains data with the top rows defined as a Criteria range for an Advanced filter. Here's the code to do the Adv Filter:

Range(Cells(6, 1), Cells(lastrowwithdata, 15)).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range(Cells(1, 1), Cells(4, 15)), Unique:=False

(there's another procedure that yields the lastrowwithdata value).

How can I have VBA then select only the visible data result of the Advanced Filter? Running into problems since the row numbers of the result are of course mixed and out of sequence. I couldn't figure out how to have the above "Range" selected either by defining it as a variable, or somehow attaching the ".select" to it.

What I want to do is select the result of the Adv Filter, copy that data, and paste it again into another worksheet so the row numbers will be then in sequence.

Thanks for any suggestions!

 

[Tom Urtis]

Re: How to have VBA select only visible results of Adv Filte

Try declaring your filtered range as a variable, then filtering it, then selecting the visible cells in the range that is redefined by offsetting one row and resizing the range by one less row. Example:

Dim FilterRange As Range
Set FilterRange = Range(Cells(6, 1), Cells(lastrowwithdata, 15))
FilterRange.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range(Cells(1, 1), Cells(4, 15)), Unique:=False
With FilterRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
End With

 

[Von Pookie]

I usually just assign the entire range of data to a variable. Then after you run the filter, you could use something like DataRange.SpecialCells(xlcelltypevisible).copy

This should only copy the visible rows in the area determined by "DataRange."

You can do some other things with SpecialCells as well. They're listed in the VBA help file.