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


countif formulaexcel duplicate finderddl dclhow do i unhide rows in excelhow to protect an excel worksheetunlock protected excel fileloop vbaexcel programme templatedefine activex controlworkbook vbaexcel 2007 activex controlsexplain macros in excelhow to insert a new row in excel 2013insert button excel 2007vba excel isemptyeasy excel macrostask planner template excelucase vbashortcut key to change the sheet in excelproject management templates excelvba code for excel exampleshow to delete a header in excelfind duplicates in excel spreadsheetworksheet tab in excelexcel cell mergeproject spreadsheet template excelsql dcl commandsexcel hyperlink to pdfvba sql inserthse meeting minutes templatevba excel 2007duplicate finder in excelweekly task planner templateproject roadmap excelfilesystemobject file existsremove blanks excelworkbooks.savevba show userformvba scripting filesystemobjectmatching cells in excellearning macros in excel 2007excel vba programingexcel macro commandshow do you unhide rows in excelsub function vbacoding worksheetsexcel vba outlookexcel vba msgbox inputclearcontents vbaexcel vba commentconvert excel column width to inchesaverage vbaexcel remove headerscrum call formatremoving macros from excelexcel how to delete empty rowsagenda excel templaterecording macrosopen xlsx files in excel 2003text box controlinsert button excel 2007remove duplicates from excel spreadsheetmysql oledb connection stringexcel sum vlookupexcel merge columnsquery sql from excelfilesystemobject deletefilesql update vbapurge excel fileabout excel formulas with exampleadvanced functions in excelsort az excelhow to unhide in excel 2013