CODEWITHSUNDEEP
vbaexcelmacros
Pawel
Pawel

Reputation: 555

Macro run only on visible cells

I was wondering how do I make macro run only on visible(unhidden) cells? what should I add to this code:?

Dim cell As Range 

For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeVisible) 
    With Application.FindFormat.Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 16711935 
        .TintAndShade = 0 
        .PatternTintAndShade = 0 
    End With 
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=True).Activate 
Next cell 
Exit Sub

Upvotes: 0

Views: 1884

Answers (2)

user6432984
user6432984

Reputation:

This will Activate all visible the cells with a specific format.

Sub Example()

    Dim SearchRange As Range
    Dim c As Range, FoundRange As Range
    Dim firstAddress As String

    On Error Resume Next

    Set SearchRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If Not SearchRange Is Nothing Then

        With Application.FindFormat.Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16711935
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        Set c = SearchRange.Find(What:="", After:=SearchRange.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
                                 xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
              , SearchFormat:=True)

        If Not c Is Nothing Then
            firstAddress = c.Address
            Set FoundRange = c
            Do
                Set c = SearchRange.Find(What:="", After:=c, LookIn:=xlFormulas, LookAt:= _
                                         xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                      , SearchFormat:=True)

                If Not c Is Nothing Then
                    'Do Something

                    Set FoundRange = Union(FoundRange, c)
                End If
            Loop While Not c Is Nothing And c.Address <> firstAddress

            FoundRange.Activate
        End If

    End If

End Sub

Upvotes: 1

Alex
Alex

Reputation: 97

If it's not checking formulas, you can change

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)

to

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants)

or if it's checking formulas, change 

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)

to

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeFormulas)

This will keep it from running on blank cells that aren't hidden.

Upvotes: 1

Related Questions