Run Macro on Two worksheet

[aaronr]

I'm have issue running this code. I keep getting getting a compile error: for without Next. Can you guys please help.

Sub DeleteRows_BASEMENT(IRibbonControl)
Dim sht As Worksheet, myShts As Variant
myShts = Array("PARENT VIEWS", "Child Sheet Index") '<-- list all the sheet names you want the code to run on here
For i = LBound(myShts) To UBound(myShts)
If i = LBound(myShts) Then
Sheets(myShts(i)).Select Replace:=True
Else
Sheets(myShts(i)).Select Replace:=False
End If
For Each sht In ActiveWindow.SelectedSheets
Last = Cells(Rows.Count, "D").End(xlUp).Row
For a = Last To 1 Step -1
If (Cells(a, "D").Value) Like "*BASEMENT*" Then
Cells(a, "D").EntireRow.Delete
Exit For
End If
Next a
End Sub

 

[Trebor76]

This is why indenting helps as it showed me you were missing two next's i.e. Next i and Next sht:

Sub DeleteRows_BASEMENT(IRibbonControl)
    Dim sht As Worksheet, myShts As Variant
    myShts = Array("PARENT VIEWS", "Child Sheet Index") '<-- list all the sheet names you want the code to run on here
    For i = LBound(myShts) To UBound(myShts)
        If i = LBound(myShts) Then
            Sheets(myShts(i)).Select Replace:=True
        Else
            Sheets(myShts(i)).Select Replace:=False
        End If
    Next i
    For Each sht In ActiveWindow.SelectedSheets
        Last = Cells(Rows.Count, "D").End(xlUp).Row
        For a = Last To 1 Step -1
            If (Cells(a, "D").Value) Like "*BASEMENT*" Then
                Cells(a, "D").EntireRow.Delete
                Exit For
            End If
        Next a
    Next sht
End Sub

HTH

Robert

 

[aaronr]

Robert thanks for your help. I see What you mean about the indenting. When I run the macro it leaves the tabs highlight. Is there a way added code to unhighlight the tabs? Thanks again

 

[Trebor76]

Is there a way added code to unhighlight the tabs

Selecting one of tabs would do it i.e.

Sheets("PARENT VIEWS").Select

 

[aaronr]

OK. i'm not sure what I'm missing. It delete the row with Basement on Parent Views but it doesn't do the Child Sheet Index.

This is why indenting helps as it showed me you were missing two next's i.e. Next i and Next sht:

Sub DeleteRows_BASEMENT(IRibbonControl)
    Dim sht As Worksheet, myShts As Variant
    myShts = Array("PARENT VIEWS", "Child Sheet Index") '<-- list all the sheet names you want the code to run on here
    For i = LBound(myShts) To UBound(myShts)
        If i = LBound(myShts) Then
            Sheets(myShts(i)).Select Replace:=True
        Else
            Sheets(myShts(i)).Select Replace:=False
        End If
    Next i
    For Each sht In ActiveWindow.SelectedSheets
        Last = Cells(Rows.Count, "D").End(xlUp).Row
        For a = Last To 1 Step -1
            If (Cells(a, "D").Value) Like "*BASEMENT*" Then
                Cells(a, "D").EntireRow.Delete
                Exit For
            End If
        Next a
    Next sht
End Sub

HTH

Robert

 

[Trebor76]

It delete the row with Basement on Parent Views but it doesn't do the Child Sheet Index.

There's a lot of code just for that

I'm not sure what the IRibbonControl parameter is for but try this (I've added some notes):

Sub DeleteRows_BASEMENT(IRibbonControl)

    Dim wsMySheet As Worksheet
    Dim lngMyRow  As Long
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In Sheets(Array("PARENT VIEWS", "Child Sheet Index"))
        For lngMyRow = wsMySheet.Cells(Rows.Count, "D").End(xlUp).Row To 1 Step -1
            If wsMySheet.Range("D" & lngMyRow).Value Like "*BASEMENT*" Then 'Case sensitive
                wsMySheet.Rows(lngMyRow).EntireRow.Delete
                Exit For 'Quits after first deletion
            End If
        Next lngMyRow
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[aaronr]

Robert,
It worked like a Charm after i delete this line (Exit For 'Quits after first deletion) from the code. Thanks for your help.

IRibbonControl parameter is used to talk to your custom Ribbon.

Link to website - http://www.rondebruin.nl/win/s2/win001.htm

again Thanks for the help.

 

[Trebor76]

It worked like a Charm after i delete this line (Exit For 'Quits after first deletion) from the code.

I thought that line from your original code was odd - that's why I wrote the comment next to it

Glad we got it sorted in the end.

Robert