Row deletion on named sheets

Question

I am trying to delete a row containing ISIN Value in a workbook in multiple sheets.

I am able to delete the ISIN in couple of sheets but not Spread calibration sheet and fuel& quality sheet.

Sub del()
    Dim num As Integer
    Dim del As String
    Dim q As Integer
    Dim a As Integer
    Dim i As Integer

    num = Range("A" & Rows.Count).End(xlUp).Row
    del = InputBox("Enter ISIN", "ISIN")
    a = Application.Worksheets.Count

    For q = 1 To a
        Worksheets(q).Activate
        If ActiveSheet.Name = "Price Selection for Upload" Then
            For i = 1 To num
                If Range("A" & i).Value = del Then
                    ActiveSheet.Rows(i).EntireRow.Delete
                End If
            Next i
        ElseIf ActiveSheet.Name = "Main Sheet" Then
            For i = 1 To num
                If Range("A" & i).Value = del Then
                    ActiveSheet.Rows(i).EntireRow.Delete
                End If
            Next i
        ElseIf ActiveSheet.Name = "Spread Calibration" Then
            For i = 1 To num
                If Range("A" & i).Value = del Then
                    ActiveSheet.Rows(i).EntireRow.Delete
                End If
            Next i
        ElseIf ActiveSheet.Name = "Fuel& Quality" Then
            For i = 1 To num
                If Range("B" & i).Value = del Then
                    ActiveSheet.Rows(i).EntireRow.Delete
                End If
            Next i
        ElseIf ActiveSheet.Name = "Sheet3" Then
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
            ActiveCell.Value = del
        End If
    Next q

    Worksheets("Email Details").Activate
    Worksheets("Email Details").Range("Q2").Select

End Sub

score 0 · Answer 1 · answered Feb 18 '20 at 09:45

Try this. You can shorten your loop by combining cases with the same outcome. I have used Select Case but you could stick with If, the point being that you don't need to repeat everything for every sheet name.

More importantly, when deleting loop backwards otherwise you risk skipping rows.

Also, looping through each row is slow so consider using Find or Autofilter.

Avoid using Select/Activate.

If the sheet names are not being properly picked up check for trailing or leading spaces etc.

Sub del()

Dim num As Long 'use Long rather than integer
Dim del As String
Dim q As Long
Dim a As Long
Dim i As Long

num = Range("A" & Rows.Count).End(xlUp).Row 'need to add sheet reference
del = InputBox("Enter ISIN", "ISIN")
a = Application.Worksheets.Count

For q = 1 To a
    With Worksheets(q)
        Select Case .Name
            Case "Price Selection for Upload", "Main Sheet", "Spread Calibration"
                For i = num To 1 Step -1 'loop backwards
                    If .Range("A" & i).Value = del Then
                        .Rows(i).EntireRow.Delete
                    End If
                Next i
            Case "Fuel& Quality"
                For i = num To 1 Step -1
                    If .Range("B" & i).Value = del Then
                        .Rows(i).EntireRow.Delete
                    End If
                Next i
            Case "Sheet3"
                .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = del
        End Select
    End With
Next q

Application.Goto Worksheets("Email Details").Range("Q2")

End Sub

Row deletion on named sheets

1 Answers1