[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel macro to delete rows of data based on a condition

Posted on 2014-08-23
2
Medium Priority
?
335 Views
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
Range("1:5").EntireRow.Delete
End If
Next
0
Comment
Question by:dbfromnewjersey
2 Comments
 
LVL 9

Accepted Solution

by:
McOz earned 2000 total points
ID: 40280681
With the following, you're saying "delete the first five rows of the sheet". There is no reference to the actual row being tested:
Range("1:5").EntireRow.Delete

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
Rows(I).Delete
'decrement value of lastrow since we deleted a row
lastrow = lastrow - 1
Else
'increment counter to go to next row
I = I + 1
End If
Loop

Open in new window

0
 

Author Comment

by:dbfromnewjersey
ID: 40280720
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).
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

865 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