Establishing which rows are currently visible due to filtering

  • Thread starter Thread starter aine_canby
  • Start date Start date
A

aine_canby

Hi,

I have an excel sheet with three colums, one called value, one called
status, and one called year. Value is simple a number and status is
one of 4 values: Implemented, not implemented, pending, canceled. I
have a filter on all three colums so that I can view selected data. I
also have a button which prints this table to a text file, by simply
stepping down through each line in the excel sheet. But how would I go
about writng only those values that are currently visible due to the
current filter choices? For example, if I wished to write only those
rows with status implemented and year 2005.

It would be nice if each row currently visible had a flag set on it. I
could the check this flag. Anyway, how would I go about solving this
problem?

Thanks,

Aine.
 
Aine, take a look at Range.SpecialCells(xlCellTypeVisible).Select in some
form.

There is also a (related?) command, Select Visible Cells, that can be added
to a toolbar (since it is not available in any standard menu or toolbar).

Regards,
George
 
Hi Aine,

As George mentioned you can use SpecialCells but...
It would be nice if each row currently visible had a flag set on it.
Click to expand...

- each row has just such a property !

Sub test()
Dim rngRow As Range
Dim ws As Worksheet
Set ws = ActiveSheet

If ActiveSheet.AutoFilterMode Then

For Each rngRow In ActiveSheet.AutoFilter.Range.Rows

Debug.Print rngRow.Row, rngRow.Address(0, 0), Not rngRow.EntireRow.Hidden
Next

End If
End Sub

Press Ctrl-g to view the Immediate window and the debug results

Regards,
Peter T
 
Hi Aine,

As George mentioned you can use SpecialCells but...


- each row has just such a property !

Sub test()
Dim rngRow As Range
Dim ws As Worksheet
Set ws = ActiveSheet

If ActiveSheet.AutoFilterMode Then

For Each rngRow In ActiveSheet.AutoFilter.Range.Rows

Debug.Print rngRow.Row, rngRow.Address(0, 0), Not rngRow.EntireRow.Hidden
Next

End If
End Sub

Press Ctrl-g to view the Immediate window and the debug results

Regards,
Peter T








- Visa citerad text -
Click to expand...

Cool, thanks for that!
 
Back
Top