Establishing which rows are currently visible due to filtering

[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.

 

[G Lykos]

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

 

[Peter T]

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.

- 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

 

[aine_canby]

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 -

Cool, thanks for that!