VBA Macro to Delete all Rows that contain a value (Excel 2010 VBA Training)
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
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