Excel macro to delete rows of data based on a condition

Posted on 2014-08-23
Last Modified: 2014-08-23
I have the following routine in an Excel macro that I'm trying to use to delete all rows in the active range where the value in column "A" is NOT equal to "California".  In other words, I'm trying to retain all records where the value in column "A" equals "California" and delete all other rows.  For some reason, the routine is deleting everything including the "California" records. There are definitely records with the value of "California" in column "A"; as I did a test run using a messagebox to display what is in the cell. What am I doing wrong? By the way, the only columns containing data are columns "A" through "E" ( 5 columns ).

Dim lastrow As Long, I As Long

lastrow = Range("F" & ActiveSheet.Rows.Count).End(xlUp).Row
For I = 1 To lastrow
If Cells(I, 1) <> "California" Then
End If
Question by:dbfromnewjersey
    LVL 9

    Accepted Solution

    With the following, you're saying "delete the first five rows of the sheet". There is no reference to the actual row being tested:

    Open in new window

    Also, keep in mind that when you are iterating through rows, if you delete a row it is replaced by the next one in the sheet. So going to the next row after deleting means you are effectively skipping one.
    Try something like this instead:
    Dim lastrow As Long, I As Long
    'get last row
    lastrow = Range("F" & ActiveSheet.Rows.Count).End(xlUp).Row
    Do While I <= lastrow
    If Cells(I, 1) <> "California" Then
    'decrement value of lastrow since we deleted a row
    lastrow = lastrow - 1
    'increment counter to go to next row
    I = I + 1
    End If

    Open in new window


    Author Comment

    OK. Thank you. I thought by coding Range("1:5") that I was saying the range of data for a particular row was in columns 1 through 5 (i.e. A through F).

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now