Excel Articles

Excel Macro VBA code to delete rows in excel sheet meeting multiple conditions

Macro VBA code to delete rows in excel sheet meeting multiple conditions

1. Add a button to a new sheet

2. Right click on button and Click on Assign Macro

3. Put below code in the Macro. This code will delete rows from Sheet “Employee” in Current Workbook with Column L having value “1” and Column H having value “Santosh”. Loop from Last row to First row

Sub Button1_Click()

Dim Firstrow As Long
 Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim deleteFlag As Boolean
On Error Resume Next

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
Set gd = ThisWorkbook
Set ws = gd.Sheets(“Employee”)
    ‘Using Sheet(“Employee”) in Current Workbook
 
    With ws

        ‘We select the sheet so we can change the window view
        .Select

        ‘If you are in Page Break Preview Or Page Layout view go
        ‘back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        ‘Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        ‘Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
       
        ‘We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
           deleteFlag = False
            ‘We check the values in the A column in this example
            With .Cells(Lrow, “L”)

                If Not IsError(.Value) Then

                    If .Value = “1” Then deleteFlag = True
                    ‘This will set delete flag for each row with the Value “1”
                    ‘in Column L.

                End If

            End With
           
            With .Cells(Lrow, “H”)

                If Not IsError(.Value) Then

                    If .Value = “Santosh” Then deleteFlag = True
                    ‘This will set delete flag for each row with the Value “Santosh”
                    ‘in Column H, case sensitive.

                End If

            End With
           
           
           
            If deleteFlag = True Then .Rows(Lrow).EntireRow.Delete

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

 

MsgBox (“Done”)

End Sub

Leave a Reply