REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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
Effortlessly Manage Your Projects and Resources
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.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

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

  2. 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
    
  3. 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

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

    Thanks it Helps

  6. 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