VBA: delete all rows code

[misszine]

Hello,

I am trying to do a macro that would loop through desired sheets (ie. sheet 1, 2, 3... but not sheet 4) and run this code:

 Sub clearcells()
 '//Declare variables//
    Dim varDelItem As Variant
    Dim lngRowStart As Long, _
        lngRowLast As Long, _
        lngRowActive As Long
    Dim strMyCol As String
    Dim rngDelRange As Range
    
    '//Set variables//
    varDelItem = "Indicateur"
    lngRowStart = 9 'Initial data row.  Change to suit.
    strMyCol = "B" 'Column containing relevant data.  Change to suit.
    lngRowLast = Cells(Rows.Count, strMyCol).End(xlUp).Row
    
    application.ScreenUpdating = False
        
    For lngRowActive = lngRowStart To lngRowLast
        If Cells(lngRowActive, strMyCol) <> varDelItem Then
            'Cater for initial setting of 'rngDelRange' range
            If rngDelRange Is Nothing Then
                Set rngDelRange = Cells(lngRowActive, strMyCol)
            Else
                Set rngDelRange = Union(rngDelRange, Cells(lngRowActive, strMyCol))
            End If
        End If
    Next lngRowActive
        
    'If the 'rngDelRange' range has been set (i.e. has something in it), then...
    If Not rngDelRange Is Nothing Then
        '...delete the rows within it.
        rngDelRange.EntireRow.Delete xlShiftUp
    'Else...
    Else
        '...inform the user that no rows are to be deleted as there was no _
        matching criteria in the dataset.
        MsgBox "No rows were deleted as every Row in Column " & strMyCol & " matched """ & varDelItem & """.", vbExclamation, "Delete Row Editor"
    End If
    
    application.ScreenUpdating = True
End Sub

Could anyone help me please??

Thank you!

 

[mumps]

Hi misszine. Try the following code. It loops throught all the sheets except for "Sheet4". This code uses the 'With...End With' statements so I have had to add dot notation to your code. Hopefully, I haven't missed any dots. If I've got them all and your code was working properly on one sheet, then it should work for all the others.

Sub clearcells()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
    If ws.Name <> "Sheet4" Then
        With ws
            '//Declare variables//
            Dim varDelItem As Variant
            Dim lngRowStart As Long, _
                lngRowLast As Long, _
                lngRowActive As Long
            Dim strMyCol As String
            Dim rngDelRange As Range
    
            '//Set variables//
            varDelItem = "Indicateur"
            lngRowStart = 9 'Initial data row.  Change to suit.
            strMyCol = "B" 'Column containing relevant data.  Change to suit.
            lngRowLast = .Cells(Rows.Count, strMyCol).End(xlUp).Row
    
                For lngRowActive = lngRowStart To lngRowLast
                    If .Cells(lngRowActive, strMyCol) <> varDelItem Then
                    'Cater for initial setting of 'rngDelRange' range
                        If rngDelRange Is Nothing Then
                            Set rngDelRange = .Cells(lngRowActive, strMyCol)
                        Else
                            Set rngDelRange = Union(rngDelRange, .Cells(lngRowActive, strMyCol))
                        End If
                    End If
                Next lngRowActive
        
            'If the 'rngDelRange' range has been set (i.e. has something in it), then...
            If Not rngDelRange Is Nothing Then
                '...delete the rows within it.
                rngDelRange.EntireRow.Delete xlShiftUp
            'Else...
            Else
                '...inform the user that no rows are to be deleted as there was no _
                    matching criteria in the dataset.
                MsgBox "No rows were deleted as every Row in Column " & strMyCol & " matched """ & varDelItem & """.", vbExclamation, "Delete Row Editor"
            End If
    End If
    Next ws
    Application.ScreenUpdating = True
End Sub