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

VBA Delete Rows Based on Cell Value Excel Macro Example Code

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

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: VBATags: Last Updated: June 17, 2022

14 Comments

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

    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

  2. PNRao June 25, 2014 at 11:44 PM

    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!

  3. Pooja June 3, 2015 at 12:48 PM

    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

  4. PNRao June 4, 2015 at 12:25 AM

    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://analysistabs.com/download/vba-delete-rows-based-on-conditions-download-example-file/

    Thanks-PNRao!

  5. Dan March 8, 2016 at 3:55 PM

    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.

  6. Alain Auguste April 27, 2016 at 7:24 PM

    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!

  7. Alain Auguste May 3, 2016 at 8:04 AM

    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

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

    Any help would be greatly appreciated!! Thank you!

  9. Moses May 9, 2016 at 6:21 PM

    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

  10. Prajith September 29, 2016 at 10:48 PM

    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

  11. Tahnee February 9, 2017 at 6:49 AM

    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.

  12. Chris March 3, 2017 at 9:18 PM

    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

  13. Murugan June 13, 2017 at 2:43 AM

    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

  14. RAHUL KAMBLE December 15, 2020 at 1:33 AM

    Really appreciate your work..!!!
    Just need one help.. I want to delete entire row from table
    if date is less then today’s date. I am facing problem as I am having some bank cells also between in column.

Leave A Comment