Macro: Select visible cells only

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David T - 15 Feb 2007 22:00 GMT

Right now my macro is creating a new worksheet for each name in a range.
However, i want my macro to create only a new worksheet for each VISIBLE name
in a range because I filter this list. Please help!!!!

Sub name_sheets()
'will add a sheet, and name it
'for each name in column C
'from C6 down till it hits a blank row
Dim Rng As Range
Dim ListRng As Range
Dim LRow As Long

Set ListRng = Range(Range("c6"), Range("c6").End(xlDown))
For Each Rng In ListRng
If Rng.Text <> "" Then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
End If
Next Rng
End Sub

Reply to this Message

Trevor Shuttleworth - 15 Feb 2007 22:18 GMT

Set ListRng = Range(Range("c6"),
Range("c6").End(xlDown)).SpecialCells(xlCellTypeVisible)

Regards

Trevor

> Right now my macro is creating a new worksheet for each name in a range.
> However, i want my macro to create only a new worksheet for each VISIBLE
[quoted text clipped - 18 lines]
> Next Rng
> End Sub

Reply to this Message

Charles Chickering - 15 Feb 2007 22:23 GMT

Change This:
If Rng.Text <> "" Then
To This:
If Rng.Text <> "" And Not Rng.EntireRow.Hidden Then

Signature

Charles Chickering

"A good example is twice the value of good advice."

> Right now my macro is creating a new worksheet for each name in a range.
> However, i want my macro to create only a new worksheet for each VISIBLE name
[quoted text clipped - 17 lines]
> Next Rng
> End Sub

Reply to this Message