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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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
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. March 15, 2014 at 12:07 AM - Reply

    […] Delete columns this way: […]

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


Related pages


excel unlock cellsvba word macroseliminate duplicate rows in excelexcel vba new worksheetproject management checklist template excelhow to delete rows in excelvlookup command in excelunlock excel spreadsheet macrosql basics interview questions and answers pdffso filecopyexcel vba with selectionhide columns in excel 2007run a macro in excelexcel bar chart exampleshow to change the width of a column in excelsample vbscript codevlookup in microsoft excelexcel checkbox macrohard return excelvlookup demomsgbox vba exampleexcel vba screenupdatingvbscript examples programspassword protect excel 2007 workbookexcel construction schedule templateexcel vba if statement multiple conditionsvb msgbox syntaxmicrosoft word macro examplesif iserror vlookup excel 2010project timelines exceltesting questions for freshersmicrosoft excel chart wizardnested if in vbscriptvlookup in microsoft excelexcel vba userform combobox additemexcel template downloadsexcel project plan templatevlookup excel 2010 examplelearn formulas in excelsales dashboard examplesexcel unlock spreadsheetswot excel templateexcel vba sql servervba excel activatehow to do pivot table in excelthe vlookup functionrecording excel macrosexcel list boxvba existssyntax excel definitionextract hyperlink from exceluserforms excelexcel dashboardingvba access connection stringvlookup vba examplems excel formulas shortcut keysms excel vba programminghow to check duplicates in excelmsgbox in accesshow to sumif in excelexcel dynamic dashboardunshare excel spreadsheetvb coding examplesvba chart objectc language interview questions and answers pdfcombo box vba excelmeeting minutes template microsoft wordhow to delete numbers in excelvba for pivot tableswhere is the pivot table in excel 2010automatically insert rows in excelexcel lookup examplescreating a dashboard in excel 2007excel offset exampleexcel application.getopenfilenamesendmail subjectsum if in excelexcel vba remove duplicatesvba excel color