Deleting rows based on multiple filter options

[rochytall2000]

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

 

[cstimart]

Does Column A, or any other column have data in it 100% of the time?

 

[sanmaya]

Can u please upload the demo file of yours?

 

[cstimart]

I am assuming that Column A has data 100% of the time, and the 5 Columns are B-F

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

 

[rochytall2000]

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

 

[rochytall2000]

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]

Does Column A contain data for each record 100% of the time?

 

[rochytall2000]

Yes, column A is populated 100% of the time, it contains a unique reference number.

 

[cstimart]

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.

 

[rochytall2000]

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.