Can you skip rows that are hidden in a worksheet. I need WS Option1 to find all words Safe

[slohman]

[COLOR=#333333]Sub MoveOption_to_Safeplay()[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Dim SheetName As String
SheetName = "Option1"
Dim i As Long
Dim MyCol As Integer
Dim MyRow As Integer

Application.ScreenUpdating = False

ActiveSheet.Unprotect

Sheets("Safeplay").Range("B17:L40").ClearContents
   
LR = Sheets(SheetName).Range("D" & Rows.Count).End(xlUp).Row
MyCol = 2
MyRow = 17

    For i = 7 To 900
        If Sheets(SheetName).Range("D" & i).Value = "Safeplay" Then
            Do Until Sheets("Safeplay").Cells(MyRow, MyCol).Value = "" Or MyRow > 40
                MyRow = MyRow + 1
            Loop
            If MyRow <= 40 Then
                Sheets("Safeplay").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("A" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
            Exit For
        End If
        End If
    Next i

LR = Sheets(SheetName).Range("D" & Rows.Count).End(xlUp).Row
MyCol = 4
MyRow = 17
    For i = 7 To 900
        If Sheets(SheetName).Range("D" & i).Value = "Safeplay" Then
            Do Until Sheets("Safeplay").Cells(MyRow, MyCol).Value = "" Or MyRow > 40
                MyRow = MyRow + 1
            Loop
            If MyRow <= 40 Then
                Sheets("Safeplay").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("C" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
            Exit For
        End If
        End If
    Next i

LR = Sheets(SheetName).Range("D" & Rows.Count).End(xlUp).Row
MyCol = 6
MyRow = 17
    For i = 7 To 900
        If Sheets(SheetName).Range("D" & i).Value = "Safeplay" Then
            Do Until Sheets("Safeplay").Cells(MyRow, MyCol).Value = "" Or MyRow > 40
                MyRow = MyRow + 1
            Loop
            If MyRow <= 40 Then
                Sheets("Safeplay").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("E" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
            Exit For
        End If
        End If
    Next i

LR = Sheets(SheetName).Range("D" & Rows.Count).End(xlUp).Row
MyCol = 8
MyRow = 17
    For i = 7 To 900
        If Sheets(SheetName).Range("D" & i).Value = "Safeplay" Then
            Do Until Sheets("Safeplay").Cells(MyRow, MyCol).Value = "" Or MyRow > 40
                MyRow = MyRow + 1
            Loop
            If MyRow <= 40 Then
                Sheets("Safeplay").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("F" & i).Value
                MyRow = MyRow + 1
            Else
                MsgBox "You have ran out of room.  Some entries were not copied"
            Exit For
        End If
        End If
    Next i
   
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   
    Sheets("Safeplay").Range("N2").Select

Application.ScreenUpdating = True
 </code>[COLOR=#333333]End Sub[/COLOR]

http://www.excelforum.com/showthread.php?t=1086761&p=4094435#post4094435

http://www.mrexcel.com/forum/excel-questions/859601-can-you-skip-rows-hidden-worksheet-i-need-ws-option1-find-all-words-safe.html#post4177390

 

[onlyadrafter]

Hello,

You would need to do something like this

If Range("D" & i).Value = "Safeplay" And Rows(i).Hidden = False Then