Deleting rows based on multiple filter options

Nagartath

New Member
Joined
Sep 15, 2006
Messages
19
Hi, I need to review a 400,000 row spreadsheet and remove all records where 5 columns are populated with data so I am only eft with records that are missing information.

I haven't got the slightest clue how to go about this using VB.

Any and all help very much appreciated
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
Does Column A, or any other column have data in it 100% of the time?
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
I am assuming that Column A has data 100% of the time, and the 5 Columns are B-F

Code:
Sub DeleteIt()
Dim LR As Long
Dim rng As Range
LR = Cells(Rows.Count, 1).End(xlUp).Offset(-1).Row
For Each rng In [A2].Resize(LR)
    If Application.CountBlank(rng.Offset(, 1).Resize(, 5)) = 0 Then
        rng.Clear
    End If
Next rng
On Error Resume Next
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
 

Nagartath

New Member
Joined
Sep 15, 2006
Messages
19
Hi, the impacted columns are:
E
G
I
M
N

I don't know how to upload a file I'm afraid, any help on that would be helpful
 

Nagartath

New Member
Joined
Sep 15, 2006
Messages
19
Hi CSTIMART, when I run the code it deletes all rows in my database....any suggetions.....I have re-org'ed my spreadsheet to reflect B through to F are the columns I am interested in.

All help greatly appreciated
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
Does Column A contain data for each record 100% of the time?
 

Nagartath

New Member
Joined
Sep 15, 2006
Messages
19
Yes, column A is populated 100% of the time, it contains a unique reference number.
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
I am assuming that the 5 cells that are "blank" aren't truly "blank", because the code should only delete rows where all 5 of those cells are blank.
 

Nagartath

New Member
Joined
Sep 15, 2006
Messages
19
I am assuming that the 5 cells that are "blank" aren't truly "blank", because the code should only delete rows where all 5 of those cells are blank.
Thanks cstimart, I was looking for code to delete the rows where the 5 cells are not blank, if all 5 cells in the row are populated (B thru to F) I want to delete the entire row, if just one of those cells is populated I want to keep the row.

Apologies if I wasn't clear in my original request.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,956
Messages
5,447,551
Members
405,456
Latest member
melmelseh

This Week's Hot Topics

Top