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


wrote in message
ups.com...
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.

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


wrote in message
ups.com...
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.

On 15 Aug, 11:08, "Peter T" <peter_t@discussions wrote:
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

wrote in message

ups.com...



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.- Dölj citerad text -

- Visa citerad text -

Cool, thanks for that!