Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - Select Only Filtered Data

Status
Not open for further replies.

prgmrgirl

Programmer
Feb 12, 2004
119
US
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

 
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
 
Thanks PHV,

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

prgmrgirl
 
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
 
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,

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

Thanks!
prgmrgirl
 
Whoops! Make that:

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

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

Cheers!
prgmrgirl
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top