Reading autofiletered data

[jcf27]

I wrote some basic code to process data from a simple spreadsheet of 3 columns. So far, no problems.

The user incorporated an autofiler.

How do I make the code recognize only the auto filtered data as opposed to the whole sheet?

For example -- TOTRows = SheetA.UsedRange.Rows.Count still returns the total number of rows and not the ones displayed.

thanks in advance !

Jorge
(green programmer)

 

[Gavona]

Try this:
Selection.SpecialCells(xlCellTypeVisible).Rows.Count

Thanks,

Gavin

 

[jcf27]

Gavin,

Thanks for the reply. For some reason it did not work. It returned 1; where in actually, after the filter is selected, there are 200 lines or so. Here is part of my original code (I have 2 active sheets).

SheetARows = SheetA.UsedRange.Rows.Count

For Sheet_A_Row = 2 To SheetARows
(I do my stuff in here...)
Next SecuritiesRow

Based on -- Selection.SpecialCells(xlCellTypeVisible).Rows.Count ; the idea is to only loop through the visible lines. It didn't work :-(

Any other suggestions?

Thanks

Jorge

 

[Loomah]

Hi Jorge
It may depend on what you want to do but you could use something like this

Dim c As Range
For Each c In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeVisible)
    Debug.Print c.Row, c.Value
Next

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

And this ?
TOTRows = SheetA.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count

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

 

[xlbo]

Jorge - keyword in the code was SELECTION
PHV's 1 liner should do the trick for you

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question