grabbing filtered cell values

[Guest]

Using VBA in excel to spawn a powerpoint presentation and send it data from the excel sheet's cells.

My references will no longer work right when the excel sheet is filtered (it still moves all the data, not the filtered subset). The filtering is accomplished through several flag-like cells that aren't the cells of interest for sending to PPT. The filtering is someone else's project, so I need to copy the cell-range of interest, regardless of what filtering is being applied, without grabbing the filtered/hidden data. Intermediate sheet would be fine if that makes it easier (dump filtered data to its own sheet and then use that to build the powerpoint slide(s)).

If it matters, I am NOT putting an excel object into powerpoint. I'm sucking the actual cell values out and sticking them into text values of shape objects in powerpoint.

How do I deal with this filtered data?

thanks a bunch.
-Matt

 

[Ken Wright]

Following snippet is an example from a routine that applies a filter, then
selects only the visible cells and copies them:-

SrcRng is the entire dataset prior to any filtering, and once it has been filted
the specialcells option allows you to home in on just what is visible:-

With SrcRng
.AutoFilter Field:=1, Criteria1:=Cel.Value
.SpecialCells(xlCellTypeVisible).Copy NewSht.Range("A1")
End With

Just use the principle to get what you need, then do what you gotta do with the
data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Using VBA in excel to spawn a powerpoint presentation and send it data from the excel sheet's cells.

My references will no longer work right when the excel sheet is filtered (it

still moves all the data, not the filtered subset). The filtering is
accomplished through several flag-like cells that aren't the cells of interest
for sending to PPT. The filtering is someone else's project, so I need to copy
the cell-range of interest, regardless of what filtering is being applied,
without grabbing the filtered/hidden data. Intermediate sheet would be fine if
that makes it easier (dump filtered data to its own sheet and then use that to
build the powerpoint slide(s)).

If it matters, I am NOT putting an excel object into powerpoint. I'm sucking

the actual cell values out and sticking them into text values of shape objects
in powerpoint.

 

[Ken Wright]

My pleasure - You can also do it manually using Edit / Go To / Special / Visible
Cells only