Delete Rows and Columns in Excel VBA

VBA delete rows columns excel Macro helps automating delete the rows and column in Excel Worksheet using VBA code. We can delete Rows and Columns in excel using VBA if there are any unnecessary records or fields in our data. For example we may wan to delete duplicate rows in data or we may want to delete rows based on certain condition. We can delete the columns based on certain conditions.

VBA delete rows columns excel – Delete Rows

We can delete rows using Delete method of Rows. See the following examples for more details:

Deleting Rows in Excel VBA: Examples

Delete Rows in Excel VBA – Examples: Deleting a specific Row

The following example will delete Row 5 from the active worksheet.

'In this Example I am deleting Row 5
Sub sbDeleteARow()

Rows(5).Delete

End Sub

Excel VBA Examples for Deleting multiple Rows at time

The following example will delete Row 5 to 10 at a time from the active worksheet.

'In this Example I am deleting Rows 5 to 10
Sub sbDeleteARowMulti()

Rows("5:10").Delete

End Sub

VBA Examples for Deleting Rows Based on certain condition

The following example will delete all the rows between 1 to 10 which having an even number in the first Column.

Sub sbDeleteARowEvenNumbers()
Dim lRow

'Last record number
lRow = 10


'loop from last row until first row
Do While lRow >= 1

'if the cell value is even then delete the row
If Cells(lRow,1) Mod 2 = 0 Then Rows(lRow).Delete

lRow = lRow - 1
Loop
End Sub

Instructions

1. Open an Excel Workbook
2. Press Alt+F11 to open VBA Editor
3. Insert a Module from Insert Menu
4. Copy the above code and Paste in the code window
5. Save the file as macro enabled workbook
6. Press F5 to execute it

• Delete Entire Row using VBA – Excel Macro Example Code
• Delete Active row using VBA
• Delete blank rows in a range using VBA
• Delete blank rows in a table using VBA
• Delete Hidden Rows using VBA
• Delete Multiple Rows using VBA – Excel Macro Example Code
• Delete rows based on Cell Color using VBA
• Delete rows based on Cell font Color using VBA
• Delete rows based on cell value using Excel VBA
• Delete rows based on criteria using VBA
• Delete rows based on Date using VBA
• Delete rows based on multiple criteria using VBA
• Delete rows if cell contains Error value using VBA
• Delete rows if cell contains Number value using VBA
• Delete rows if cell contains string value using VBA
• Delete rows if cell is 0 (equals to zero) using VBA
• Delete row if Cell is Empty/Blank using Excel VBA
• Delete rows in a range using VBA
• Delete rows shift up using VBA
• Delete rows with specific value using VBA – Excel VBA Macro Example Code

Deleting Columns in Excel VBA

We can delete the columns using Delete property of Columns. The following examples will show you how to delete columns in various situations.

Deleting Columns in Excel VBA: Examples

Example to delete a specific Column using VBA

In this Example I am deleting Columns D from the active worksheet.

Sub sbDeleteAColumn()

Columns("D").Delete

End Sub

Deleting multiple Columns at a time in Excel using VBA

In this Example I am deleting Columns D to F from the active worksheet using VBA.

Sub sbDeleteAColumnMulti()

Columns("D:F").Delete

End Sub

Deleting Columns based on certain condition in Excel using VBA

The following example will delete the first 10 columns if they have any odd numbers in the first Row

Sub sbDeleteOddDataColumns()
Dim lCol
lCol = 10
Do While lCol >= 1
'If the cell value is odd the delete column
If Cells(1, lCol) Mod 2 <> 0 Then Columns(lCol).Delete

lCol = lCol - 1
Loop
End Sub

Instructions

1. Open an Excel Workbook
2. Press Alt+F11 to open VBA Editor
3. Insert a Module from Insert Menu
4. Copy the above code and Paste in the code window
5. Save the file as macro enabled workbook
6. Press F5 to execute it

• VBA Delete multiple Columns Excel Macro Example Code
• VBA Delete Hidden Columns Excel Macro Example Code
• VBA Delete Entire Column Excel Macro Example Code
• VBA Delete Columns with specific data – Excel Macro Example Code
• VBA Delete Columns shift left – Excel Macro Example Code
• VBA Delete Columns in range Excel Macro Example Code
• VBA Delete Columns if Cell is Empty – Excel Macro Example Code
• VBA Delete Columns if cell is 0 (equals to zero) Excel Macro Example Code
• VBA Delete Columns if cell contains string – Excel Macro Example Code
• VBA Delete Columns if cell contains Number Excel Macro Example Code
• VBA Delete Columns if cell contains Error Excel Macro Example Code
• VBA Delete Columns based on multiple criteria – Excel Macro Example Code
• VBA Delete Columns based on Date – Excel Macro Example Code
• VBA Delete Columns based on Criteria – Excel Macro Example Code
• VBA Delete Columns based on Cell Color – Excel Macro Example Code
• VBA Delete Columns based on cell value – Excel Macro Example Code
• VBA Delete blank Columns in a table
• Delete blank Columns in a range using VBA
• Delete Active Column using VBA