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

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
Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: May 7th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

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.

6 Comments

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

    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

      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

    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

      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
      
  3. Pavan August 14, 2018 at 1:48 PM

    Thanks it Helps

  4. Eduard Roy A. Sumacot March 9, 2019 at 10:14 AM

    Whats wrong with this
    if i selected the 2nd data i want to delete it cant.
    the result is it deleted the 1st data

    Private Sub cmdDelete_Click()

    Dim i As Integer

    For i = 2 To Range(“A65356”).End(xlUp).Row-1
    If ListBox1.Selected(i) Then
    Rows(i).Select
    Selection.Delete

    End If
    Next i

    End Sub

Leave A Comment