last cell in filter

S

scrabtree

I want to use the filter option to filter several columns
of data. I then want a code that will select all the
filtered data from cell A1 to the last cell in the last
row and column of the filtered data.
 
T

Tom Ogilvy

If this is an autofilter and the headers are in row1, starting in column 1
then

Dim rng as Range, rng1 as Range
set rng = Activesheet.autofilter.Range.Columns1
set rng1 = rng.specialCells(xlVisible)
set rng1 = intersect(rng,rng1.EntireRow)
rng1.Select

If you just want to copy the visible cells


Dim rng as Range
set rng = Activesheet.autofilter.Range
rng.copy Destination:=Worksheets("Sheet2").Range("A1")

as an example. The default behavior is to only copy the visible cells.
 
Top