Delete rows based on cell value using Excel VBA

Home/VBA/Delete rows based on cell value using Excel VBA

VBA code to delete rows based on cell value example will help us to delete rows based on a cell value from excel worksheet. We can use Delete method of Rows to delete the rows if the cell value matches the given value. In this example we will see how to delete the rows in excel worksheet using VBA based on cell value. VBA code for deleting rows based on cell value macro should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.

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 code to delete rows based on cell value


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets based on cell value. This will help you to know how to delete specific rows based on a cell value from Excel workbook using VBA.

VBA Delete rows based on cell value: Syntax


Following is the VBA syntax and sample VBA code to delete rows based on cell value from worksheet using VBA. We are using the Delete method of the Rows object of worksheet.


If Then Rows(“

[Row Numbers]”).EntireRow.Delete

Here cell value criteria is the condition which you want to check the cells to delete rows. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

: Delete rows based on cell value using VBA: Examples


The following VBA code is to delete rows based on cell value from the excel worksheet. This code will delete the rows (1 to 20) if cell value is 10.

Sub sbDelete_Rows_Based_On_Criteria()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1) = 10 Then
Rows(iCntr).Delete
End If
Next
End Sub

: Instructions to run the VBA code to delete rows based on cell value


Please follow the below steps to execute the VBA code to delete rows based on cell value from Excel worksheets.
Step 1: Open any Excel workbook
Step 2: Press Alt+F11 – This will open the VBA Editor
Step 3: Insert a code module from then insert menu
Step 4: Copy the above code and paste in the code module which have inserted in the above step
Step 5: Enter some sample data in first column from row 1 to 20
Step 6: Now press F5 to execute the code

Now you can observe that the rows are deleted from worksheet if the cell value is 10.

: Explained VBA Code to Delete Rows based on cell value

Starting Program and sub procedure to write VBA code to delete rows based on cell value.
Sub sbDelete_Rows_Based_On_Cell_Value()

‘Declaring the variable lRow as long to store the last row number.
Dim lRow As Long

‘Declaring the variable iCntr as long to use in the For loop.
Dim iCntr As Long

‘Assigning the last row value to the variable lRow.
lRow = 20

‘Using for loop.
‘We are checking the each cell value if it cell value equals 10.
‘And deleting the row if true.
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1) = 10 Then
Rows(iCntr).Delete
End If
Next

‘Ending the macro to delete the rows based on criteria using VBA.
End Sub

Here you can observe that we are looping through the cells from bottom to up. This is the best approach to check the cell values and then delete the rows.

Download Example File

You can download the example file and explore the VBA code here:
vba-delete-rows-based-on-conditions-download-example-file

LIMITED TIME OFFER
By |April 21st, 2014|VBA|13 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

13 Comments

  1. Rohini June 25, 2014 at 10:18 AM - Reply

    Mr. Rao, I found your codes for deleting rows very helpful. Although I use ACL and IDEA for many of my projects, I find Excel very handy for the final analysis.
    I am trying to delete rows based on multiple criteria in column L. The macro deletes the rows but there are some rows (esp the second row) and the last row and some in between that do not get deleted. I added some sample rows with random verbiage to see if they would get deleted. I tried some variation on the criteria:
    1) Case statement with cell value in L
    2) Wild card character using If Not in conjunction with “Like “xxx’” but still the same problem.
    3) Not sure if I can use the last row/last column with this one instead of range.
    Any help is greatly appreciated.

    Thanks
    Kindest regards, Rohini

    Code: Sub DeleteMyRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, Rng As Range, Cell As Range
    Set ws = ActiveSheet
    Set Rng = ws.Range(Range(“L2”), Range(“L” & Rows.Count).End(xlUp))
    For Each Cell In Rng
    If Not Cell.Value “N. HOUSTON ROSLYN- MFG” Or _
    Not Cell.Value “MOORE MFG” Or _
    Not Cell.Value “N. HOUSTON – TURNKEY” Then
    Cell.EntireRow.Delete
    End If
    Next Cell
    Application.ScreenUpdating = true

    • PNRao June 25, 2014 at 11:44 PM - Reply

      Hi Rohini,
      We are glad to help you in solving your issue.
      Your code:
      1. You are using for condition to delete the row from starting of the range
      2. Your condition will check for the criteria in each cell in the range and delete the if satisfies

      Problem:
      Assume that L5 and L6 matches your criteria.
      When you execute the code: it will match the criteria at L5 and delete the 5th Row, now L6 value will be at L5.
      The next iteration will check for L6. so L5 is ignored, actually the current L5 (previously L6) also matches your criteria. [So, these are ignored rows which your observing that are not deleting)
      Solution:
      You can loop through the cells from bottom to top:


      Dim lRow As Long

      lRow = Range("L" & Rows.Count).End(xlUp).Row
      For iCntr = lRow To 1 Step -1
      If Not Range("L" & iCntr) = "N. HOUSTON ROSLYN- MFG" Or _
      Not Range("L" & iCntr) = "MOORE MFG" Or _
      Not Range("L" & iCntr) = "N. HOUSTON – TURNKEY" Then
      Range("L" & iCntr).EntireRow.Delete
      End If
      Next iCntr

      Hope this helps, I have explained this – Why we should delete the rows from bottom to top in one of our delete rows example. You can check if required.

      Thanks-PNRao!

  2. Pooja June 3, 2015 at 12:48 PM - Reply

    Hi,
    thanks for your code – it is very helpful for BAs like me who do not know coding 🙂

    I would require additional help from you please – I want to delete all rows in a worksheet if Column L of the sheet has a value called ‘CLOSED CONTRACTS’. I copied the code above and did some changes but it couldn’t do it successfully.

    Any help is greatly appreciated.

    Thanks

    • PNRao June 4, 2015 at 12:25 AM - Reply

      Hi Pooja,
      Thanks for your nice feedback!

      Here is the code which will help you to solve your requirement.

      Sub sbDelete_Rows_Based_On_Criteria()
      Dim lRow As Long
      Dim iCntr As Long
      lRow = Sheets("YourSheetName").Range("L60000").End(xlUp).Row
      ' Here 60000, assuming you have less than 60000 records
      ' *There are better ways to find the last row, please refer our examples
      ' YourSheetName = Your worksheet name
      For iCntr = lRow To 1 Step -1
      If Cells(iCntr, 12) = "CLOSED CONTRACTS" Then ' Here 12 is Column Number (Column L)
      Rows(iCntr).Delete
      End If
      Next
      End Sub
      

      You can download the example macro here:
      http://mongopono.ru/download/vba-delete-rows-based-on-conditions-download-example-file/

      Thanks-PNRao!

  3. Dan March 8, 2016 at 3:55 PM - Reply

    Hi. Your code is very helpful and it works ok. However could you help me make it only check column A for the criteria, becuse it seems like the macro is making too much work for my case.
    Thank you very much.

  4. Alain Auguste April 27, 2016 at 7:24 PM - Reply

    Mr. Rao,
    I am a novice at VBA Coding. I found your examples to be very helpful which is why I was hoping you can help me.
    I am trying to automate a very time consuming project in excel but I’ve been struggling to find a way to properly code this process. The project is as follows: I am trying to delete entire rows based on multiple independent criteria from three different columns.
    1) I get a monthly data dump which varies on a monthly basis which means the number of rows changes on monthly basis. How do I account for this issue in the coding?
    2) One of the criteria is to delete entire row if a column contains blank cells. How do I include the blank cells along with the other criteria?
    Any help is greatly appreciated.

    Thank you!

  5. Alain Auguste May 3, 2016 at 8:04 AM - Reply

    Sorry for all the posts, but I’ve come up with this code thus far but it does not work:

    Sub Loop_Cuts()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With

    With ActiveSheet.Select
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    .DisplayPageBreaks = False

    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    For Lrow = Lastrow To Firstrow Step -1

    With .Cells(Lrow, “I”)

    If Not IsError(.Value) Then
    Select Case .Value
    Case Is = “ASSET BACKED”, “CASH”, “CMBS”, “COMMINGKED FUND”, “CONVERTIBLES”, “CORPORATE”, “MORTGAGE PASS-THROUGH”, “MUNICIPAL”, “MUTUAL FUNDS”, “CMO”, “AGENCY”: .EntireRow.Delete Or_

    With .Cells(Lrow, “G”)

    If Not IsError(.Value) Then
    Select Case .Value
    Case Is = “CREDIT CARD”, “NON-SECY ASSET-STOCK”, “SHORT TERMS”: .EntireRow.Delete Or_

    With .Cells(Lrow, “E”)

    If Not IsError(.Value) Then
    Select Case .Value
    Case Is = “UNITED STATES OF AMERICA (THE)”: .EntireRow.Delete Or_

    With .Cells(Lrow, “E”)

    ElseIf Not IsError(.Value) Then
    Select Case .Value
    Case Is = “‘”: .EntireRow.Delete

    End Select

    End If

    End With

    Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
    End With

    End Sub

  6. Alain Auguste May 3, 2016 at 8:05 AM - Reply

    Any help would be greatly appreciated!! Thank you!

  7. Moses May 9, 2016 at 6:21 PM - Reply

    Hi,
    I need an Excel macro, to match the two excels, if two excels have the same value in a particular column, then the entire row should be deleted from the first Excel sheet.

    I want it like, we should enter the column which we need to match(A or B or C or….)

    E.g.,
    Excel 1

    A B C
    1 100 200 jack
    2 111 300 Kim
    3 112 400 katie
    4 123 500 Rosie

    Excel 2

    A B C
    1 111 300 Kim
    2 123 500 Rosie

    Result should be:

    A B C
    1 100 200 jack
    2 112 400 katie

  8. Prajith September 29, 2016 at 10:48 PM - Reply

    HI,

    I have an excel sheet in which i need to keep on the desired line items :
    For example – in the given below data when i run the macro i need only the line items with type 60 and 68

    CoCd DocumentNo Year Type
    5780 200145770 2016 68
    5780 200145892 2016 45
    5780 200145893 2016 60
    5780 200145893 2016 89

  9. Tahnee February 9, 2017 at 6:49 AM - Reply

    Is there a way to make this run continuously in the back ground?

    For example I have a drop down and every time “Delivered” is selected I want the row to deleted.

  10. Chris March 3, 2017 at 9:18 PM - Reply

    Hi,
    I want to delete all rows in column B in multiple tabs (with different row counts) with the value 0.00 and then export the first seven (7) tabs in the excel worksheet as csv files into the same folder. Can someone help me modify this code to achieve the output? The first tab contains the most rows (163)
    Thanks in advance for anyone that can assist!

    Sub ExportSheetsToCSV()
    Dim xWs As Worksheet
    Dim xcsvFile As String
    Dim lRow As Long
    Dim iCntr As Long
    lRow = 163
    For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) = 0 Then
    Rows(iCntr).Delete
    End If
    Next
    For Each xWs In Application.ActiveWorkbook.Worksheets
    xWs.Copy
    xcsvFile = CurDir & “\” & xWs.Name & “.csv”
    Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
    FileFormat:=xlCSV, CreateBackup:=False
    Application.ActiveWorkbook.Saved = True
    Application.ActiveWorkbook.Close
    Next
    End Sub

  11. Murugan June 13, 2017 at 2:43 AM - Reply

    In a dump, i need to keep only the rows which matches my other database in separate excel, for example, in my dump column A have n number of names but in my main database I have only 70 names. I just want the 70 names and rest of the rows need to be deleted

Leave A Comment


Related pages


shortcut keys of excel formulaexcel mysql connectiondim string vbanested if functions excel 2010vba code excelexcel offset examplesswot analysis template powerpointmicrosoft excel programming tutorialhow to protect excel with passwordfilesystemobject movefiledim as workbookrun vbaexcel current worksheetdelete duplicates from excelrunning vba in excelvlookup with choose functionhow to write excel macroexcel consolidate multiple workbookscombobox clearexcel vba unprotect sheetexcel option explicitexcel convert table to rangevariables in vbaaccess vba query parametersexcel password protected file forgot passwordfinding duplicate data in excelexcel look for duplicatesmsdasql downloadunprotect excel sheet 2013vlookup definitionlearn macros excelvba sheetshow to merge the cells in exceldialog box vbavba popup boxvba update status barmacro excel vbavba userform checkboxvba user defined functionoption buttons in excellock excel sheethow to remove unused cells in excelmacro in xlsexcel vba status bar progressmerge macro excelvba close userformbeginner excel tutorialmultiselect listbox vbacapacity plan template excelmeeting minutes template microsoft wordrun macro on open exceloption button excel vbaexcel ifsexcel vba copy worksheet to another workbookexcel if statement formathow to insert macro in excel 2007how do i add the developer tab to the ribbonremove duplicate records excelexcel workbook passwordunmerge cellinsert comment in excelexcel macro copy rowhow to delete blank cells in excelsqlfunctionsexcel estimating templatescombobox.items.addopen dialog box vbahow to prepare dashboard in excel 2007learning vlookup in exceldescending order excelpivot table advance