Excel - Select Only Filtered Data

[prgmrgirl]

Howdy all,

How would I programmatically select only the data in a list that has been filtered?

For example, if the user has filtered the data to only show companies with offices in IL, how would I then select that data without selecting everything else?

Thanks!
prgmrgirl

 

[PHV]

Something like this ?
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[prgmrgirl]

Thanks PHV,

But unfortunately, that selects visible cells on the entire spreadsheet, not just from the list.

prgmrgirl

 

[PHV]

A starting point:
yourRange.SpecialCells(xlCellTypeVisible).Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[prgmrgirl]

WOOT! Thanks PHV!

That was a total d'oh moment. Since ListObjects don't have a Cells property, I went about it the round about way:

where myDataList is declared as my ListObject and myRange as a Range Object,

Set myRange = myDataList.Range
myRange.Cells.SpecialCells(xlCellTypeVisible).Select

Thanks!
prgmrgirl

 

[prgmrgirl]

Whoops! Make that:

Set myRange = myDataList.DataBodyRange
myRange.Cells.SpecialCells(xlCellTypeVisible).Select

DataBodyRange gives you only the data without the header or inssert row.

Cheers!
prgmrgirl