Delete Rows and Columns in Excel VBA

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


VBA delete rows columns excel – Delete Rows

Delete Rows in Excel VBAWe 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

Deleting Columns in Excel VBA

Delete 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
LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template
LIMITED TIME OFFER - Get it Now!
Business PowerPoint Presentations Templates Pack
 
 
Related Resource External VBA Reference
By |May 7th, 2013|Excel VBA|5 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

5 Comments

  1. Sharan April 3, 2015 at 4:09 PM - Reply

    Hi,

    VBA delete rows columns excel – Delete Rows

    Can you please check why the below code is not working as mentioned ?

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

    [code language=”vb”]
    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

    Thanks & Regards,
    Sharan

    • PNRao April 7, 2015 at 8:21 PM - Reply

      Hi Sharan,

      Please try this code, I have tested and its working fine.

       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 
  2. Sean June 10, 2015 at 8:57 PM - Reply

    Hi,

    How can I delete row two and everything beyond it not knowing how many rows exactly it could be?

    Thanks,
    Sean

    • Piirtola November 21, 2015 at 5:42 PM - Reply

      Alternate code to use method to first find the last row with data.
      Like this

       Sub Delete2toLastRow() Dim LastRow As Long 'Count Rows in A column LastRow = Range("A" & Rows.Count).End(xlUp).Row If LastRow = 1 Then 'To make sure you don't delete Row 1 LastRow = 3 End If Rows("2:" & LastRow).Delete End Sub 

Leave A Comment