Deleting rows based on True/False Criteria

**CatherineN** · 06-15-2007, 01:39 PM

I am attempting to delete entire rows if a certain column is false. This column has the following function:
=IF(MID(A1,35,3)="CMD",1,"")
So, if the cell (in column R) is "", I guess, I need the row deleted, and if it is 1, then I want to keep it. I am very lame with VBA and cannot find a command to delete an entire row, and the only example codes I'm finding are to delete blank rows. I have about 12 spread sheets, and each has about 65,000 entries (I would have liked it in 2 but it wouldn't fit, maybe once I delete everything I don't need), so I don't expect it to be fast, but I need some help. Would it be easier if I made the false something other than ""?

Thanks for the help
Catherine

**mrice** · 06-15-2007, 01:48 PM

An easy way to do this is to ...

Add an additional column and fill it with sequential numbers (1,2,3 etc)
Sort by the column containing the blanks
Delete the section containing the rows with the blank columns
Re-sort by the column with the sequential numbers.
Delete the sequential numbers column

By macro you could do something like...

Const MyColumn = 5

Sub Test()
Application.ScreenUpdating = False
For N = Cells(1, 1).CurrentRegion.Count To 1 Step -1
    If Cells(N, MyColumn) = "" Then Rows(N).Delete
Next N
Application.ScreenUpdating = True
End Sub

Set MyColumn to the column of interest. The ScreenUpdating commands are to speed the code up a bit by avoiding refreshing the screen each time.

**CatherineN** · 06-15-2007, 02:40 PM

Wow, you make me realize I was making it way harder than it was, trying to write scripts. All I had to do was sort by column R and then delete everything below the break between 1 and "".

Thanks for being the catalyst to that realization

Deleting rows based on True/False Criteria

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Deleting rows based on True/False Criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions