1

I have a data set in Excel that i need to filter out rows where no data exists (i.e.- empty cell). It isn't as easy as deleting a row that has an empty cell in one column but rather if there are empty cells in three columns the entire row should be deleted. What macro would be required to execute this?

Pleas advise.

Improve this question
1
  • Welcome to Super User. We are all volunteers here and this really isn't a script/macro writing service. We would appreciate you telling us what you have already tried and researched so we can help you without repeating steps you have already taken. Especially important is to share what macro (VBA) you already have, and any errors you have encountered, so we can help you. – CharlieRB Mar 9 '15 at 20:09
1

Here's an example where the columns you want to check are A, B, and C.

Option Explicit
Sub DeleteIfBlanks()
    Dim r As Long
    Dim rng As Range
    With ActiveSheet
        For r = 1 To .UsedRange.Rows.Count
            If LenB(.Cells(r, 1)) = 0 Then
            If LenB(.Cells(r, 2)) = 0 Then
            If LenB(.Cells(r, 3)) = 0 Then
                If rng Is Nothing Then Set rng = .Cells(r, 1) Else Set rng = Union(rng, .Cells(r, 1))
            End If
            End If
            End If
        Next
        If Not rng Is Nothing Then rng.EntireRow.Delete
    End With
End Sub

You need to change the statement LenB(.Cells(r, 1)) so that the 1 corresponds to the number of whatever column you want to check for blanks.

To other VBA folks out there: I nest my IF statements for the very minor potential speed improvement. I union just the first cell and rollup to entirerow at the end because I'm lazy and I know cell notation better than row notation. If there are any improvements that are functional and not just stylistic, let me know.

Improve this answer
5
  • Your code looks good.....the only change I would suggest is to Dim r as Long – Gary's Student Mar 9 '15 at 21:11
  • @Gary'sStudent I just recently switched back to dimensioning row counters as integers. What's the benefit of long? Excel can only have 2^20 rows and the integer data type can handle numbers up to (2^31)-1. Why use the extra 4 bytes of memory to store it as long? – Engineer Toast Mar 9 '15 at 21:40
  • From VBA Help Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767. – Gary's Student Mar 9 '15 at 21:56
  • So I confused myself with the Visual Studio data types. The Office help is nonoperational on my work computer. – Engineer Toast Mar 9 '15 at 22:10
  • As I said before, I still like your approach. – Gary's Student Mar 9 '15 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.