Different options to delete rows using a macro in an excel report

Different ways to delete a row, here, I am showing you 3 manners, the “rude” way, deleting all rows you want, deleting the rows where there is an empty cell and the last one, when you find a specific word.

macro excel macro excel macro excel

 

When I use the macro ?

To delete rows that I don’t need.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

To delete all rows:

Sub test()
' change A2:A20 by the number of rows
Range("A2:A20").EntireRow.Delete
End Sub

To delete rows only with empty cells:

Sub test()
Dim i As Range
On Error Resume Next
' change A:A by the column letter
Set i = [A:A].SpecialCells(xlCellTypeBlanks).EntireRow
Intersect(i, i).Delete
ActiveSheet.UsedRange
End Sub

To delete rows only with a particular word:

Sub test()
Dim i As Range
Dim j
' change A2 and A20 by your cell referencewhere to search
Set j = ActiveSheet.Range("A2", ActiveSheet.Range("A20").End(xlUp))
Do
' change february by your word to find
Set i = j.Find("February", LookIn:=xlValues)
If Not i Is Nothing Then i.EntireRow.Delete
Loop While Not i Is Nothing
End Sub
macro excel macro excel

Interesting Management