Delete missing data from a set of 3 columns in Excel

Question

I have a dataset that includes 9 columns. I want to check each row to see if the last 3 columns are empty. If all 3 are empty, I want to delete the row. I'm currently trying to do this in VBA, but I'm a programming newb and find myself completely overwhelmed.

The pseudocode that I've written is as follows:

For Row i
If(Col 1 & Col 2 & Col 3) = blank
Then delete Row i
Move on to next Row

There are numerous examples of how to delete rows based on specific conditions here. [This answer](http://stackoverflow.com/q/37011632/4088852) for example just needs a couple `And`s in the `If` condition. — Comintern, Feb 23 '17 at 16:11
Is an empty string considered an "empty cell"? Is "empty" the cell's *value* or "empty" is any cell that's *actually* empty i.e. without even a formula in it? How to check for "empty" is quite different depending on how you clarify what "empty" means. — Mathieu Guindon, Feb 23 '17 at 16:31
Empty meaning there is nothing contained in the cell. No spaces, no data, nothing. — mathvxn, Feb 23 '17 at 20:36

score 1 · Answer 1 · answered Feb 23 '17 at 17:50

1

I'd go like follows

Dim iArea As Long

With Range("E:G") '<--| change "E:G" to your actual last three columns indexes
    If WorksheetFunction.CountBlank(.Cells) < 3 Then Exit Sub
    With .SpecialCells(xlCellTypeBlanks)
        For iArea = .Areas.Count To 1 Step -1
            If .Areas(iArea).Count Mod 3 = 0 Then .Areas(iArea).EntireRow.Delete
        Next
    End With
End With

answered Feb 23 '17 at 17:50

user3598756

28,893
4
18
28

@mathvxn, did you get through it? – user3598756 Mar 03 '17 at 07:36
I actually figured out a solution using JMP. It was simple once I found the right tool! – mathvxn Mar 22 '17 at 16:13

Cyril · Answer 2 · 2017-02-24T19:05:24.720

0

Assuming you have at least one row that is always filled out, you can use the following:

Dim LR as Long
Dim i as Integer

LR = Cells(Sheets("REF").Rows.Count,1).End(xlUp).Row

For i = 1 to 9

If Range(Cells(LR-3,i),(Cells(LR,i)).Value="" Then

    Columns(i).Delete
Else:
End If
Next i

This works by defining the last row as LR, and defining a variable as i. You will check column "i" to determine if the last 3 rows of the column are "", aka it's blank; one might try to use ISBLANK(), but that cannot work for an array. If this is true, then you will delete the column, i. The code will then move to the next i. The FOR LOOP using i starts at 1 and goes to 9, which corresponds to starting at column 1 (A) and ending at column 9 (I).

Edit:

I appear to have misread which was supposed to be empty and which is supposed to be deleted, in terms of columns/rows... this code would be re-written as:

Dim LR as Long
Dim i as Integer

LR = Cells(Sheets("REF").Rows.Count,1).End(xlUp).Row

For i = LR to 2 Step -1 'Assumes you have headers in Row1
    If AND(ISBLANK(Cells(i,7)),ISBLANK(Cells(i,8)),ISBLANK(Cells(i,9)) Then
        Rows(i).Delete
    End If
Next i

Significant changes are checking is each of the 3 last columns in the row are empty, ISBLANK(), changing that a row gets deleted if the condition is met, and changing what to loop through.

edited Feb 24 '17 at 19:05

answered Feb 23 '17 at 16:13

Cyril

6,448
1
18
31

1

What is the intended purpose of `Else:` in that code? You have a statement separator with no statement after it followed with a completely empty `Else` block. – Comintern Feb 23 '17 at 17:05
@Comintern I appear to have misread which item is getting deleted. Easy enough fix to the code. As for the Else: block, I could delete that. I pasted in a code and altered it to meet the needs of the question (seems easier than adding 4 spaces to each line!). – Cyril Feb 24 '17 at 18:55
1

Still doesn't work. Hint - [when you're deleting rows, you need to loop ***backwards***](http://stackoverflow.com/a/28861155/4088852). – Comintern Feb 24 '17 at 19:02
Just was working on that and saw I had a notification afterwards. I forgot about the step portion of the reverse loop, so thanks for the hint. I also haven't had an issue in the past if I REDIM the last row inside of the loop, but that seems to slow it down a bit. Either way, the reverse-loop is much more appropriate. – Cyril Feb 24 '17 at 19:10
1

If by "redim" you mean "find the last row again", that would have the same bug. The end point of a `For` loop is fixed when the loop starts, and it's the *iterator* that becomes mis-aligned, not the end point. – Comintern Feb 24 '17 at 19:13
Thanks for the info there, @Comintern. Will do my best to remember it going forward. – Cyril Feb 24 '17 at 19:17

pokemon_Man · Answer 3 · 2017-02-23T17:31:18.890

-1

Here's another answer, assuming your last three column starts on "G","H","I".

Sub DeleteRowWithLastThreeColumnsBlank()

    Dim N As Long, i As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To N
        If Cells(i, "G").Value = "" And Cells(i, "H").Value = "" And Cells(i, "I").Value = "" Then
            Rows(i).EntireRow.Delete
            N = Cells(Rows.Count, "A").End(xlUp).Row
        End If 
    Next i

End Sub

edited Feb 23 '17 at 17:31

answered Feb 23 '17 at 16:19

pokemon_Man

902
1
9
25

Sorry, this code won't work. What happens to the row count after you delete the first row? – Comintern Feb 23 '17 at 17:07

Delete missing data from a set of 3 columns in Excel

3 Answers3