Prevent selecting filtered rows in AutoFiltered Range

[PcolaITGuy]

I have a giant spreadsheet of server information using autofilters for the
columns. One of the uses I have for this spreadsheet is for performing
repetitive tasks by using a macro that is manually activated once I have a
range of server names selected. When the macro is activated, it takes the
selected range of server names and writes them to a text file that is later
called from a batch file.

The only problem is that filtered (hidden) rows are being included in the
selection. Is there a way to prevent this in code? Here is my code that
handles the server name selection:

Sub WriteFile()
Dim OutputFile As String
OutputFile = "C:\scripts\selection.txt"

Dim myRange As Range
Set myRange = Selection

Open OutputFile For Output As #1

For Each rngCell In myRange.Cells

inhalt = Cells(rngCell.Row, rngCell.Column).Value

Print #1, inhalt

Next rngCell

Close #1
ServerTool.Show vbModal
End Sub


Thanks in advance,

Scott

 

[George Nicholson]

Try:
For Each rngCell In myRange.Cells.SpecialCells(xlCellTypeVisible)

or, if that doesn't work for some reason,

For Each rngCell In myRange.Cells
If Not rngCell.Hidden
Print #1, rngCell.Value
End if
Next

 

[Dave Peterson]

Maybe...

set myrange = nothing
on error resume next
Set myRange = Selection.cells.specialcells(xlcelltypevisible)
on error goto 0

if myrange is nothing then
msgbox "selection is all hidden!"
exit sub
end if

ps.
Couldn't:
inhalt = Cells(rngCell.Row, rngCell.Column).Value
be:
inhalt = rngCell.Value

 

[PcolaITGuy]

Thanks guys for the advice. George...the first recommendation worked
perfectly, while the second gave me a strange pop-up that said simply "400".

I'm good to go!!

Thanks again,

Scott

 

[PcolaITGuy]

Here's the final code:

Sub WriteFile()
Dim OutputFile As String
OutputFile = "C:\scripts\selection.txt"

Dim myRange As Range
Set myRange = Selection

Open OutputFile For Output As #1
For Each rngCell In myRange.Cells.SpecialCells(xlCellTypeVisible)
inhalt = Cells(rngCell.Row, rngCell.Column).Value

Print #1, inhalt
Next rngCell

Close #1
ServerTool.Show vbModal
'servertool is my form that uses the text file created

End Sub