VBA Macro to Delete all Rows that contain a value (Excel 2010 VBA Training)

VBA Macro to Delete all Rows that contain a value  (Excel 2010 VBA Training)

Notes:

You can replace this part of the macro

Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

With this if you want to fill in the Firstrow and the Lastrow yourself.

Firstrow = 4
Lastrow = 100

Or with this if you want to fill in the Firstrow and let the code find the Lastrow in the column.

Firstrow = 1
Lastrow = .Cells(.Rows.Count, “A”).End(xlUp).Row

The code below will delete every row in the usedrange with “ron” in the A column.
If .Value = “ron” Then .EntireRow.Delete

or

Delete all rows that DO NOT have the word ron

If .Value <> “ron” Then .EntireRow.Delete

I use the A column in my example, change the A to your column in this code line.
With .Cells(Lrow, “A”)

Change ron and the A to your search word and column before you try the macro.
See the examples below the macro if you want to do something different

More examples here:

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        '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

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "ron" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

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

End Sub

4 thoughts on “VBA Macro to Delete all Rows that contain a value (Excel 2010 VBA Training)

  1. Hi! This post could not be written any better!
    Reading through this post reminds me of my old room
    mate! He always kept chatting about this. I will forward this
    write-up to him. Fairly certain he will have a good read.
    Many thanks for sharing!

  2. I like the helpful information you provide in your articles.
    I’ll bookmark your weblog and check again here regularly. I am quite certain I will learn many new stuff right here! Good luck for the next!

Leave a Reply

Your email address will not be published. Required fields are marked *