Dynamically remove blanks from a range of excel cells

Question

I have a named range of data, called 'data'. I'm trying to find a formula or array formula that will return data in a new range of cells, but will all the blank rows missing.

i.e. data is:

row   x    y    
1     A    77    
2          
3     B    23    
4     A    100    
5    

And my new range is:

row    x    y    
1     A    77    
3     B    23    
4     A    100    

It's ok if the blank rows end up at the end of the array. So far I am stumped

Solution 1

You should use the special cells method for this. Either with vba or Manually.

Manually

2007/2010

Select column A
Home Tab -Find & Select - Goto Special - Blanks - Ok
Home Tab - Delete Cells - Entire Row - Ok

VBA

Sub DeleteBlanks()
    Activesheet.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

If you want to leave the original data intact and copy the range to another sheet try something like:

Sub DeleteBlanks()

    Dim vArray As Variant
    '// Get an array of your data
    vArray = Sheet1.UsedRange
    '// Copy the data to another sheet
    Sheet2.Range("A1").Resize(UBound(vArray, 1), UBound(vArray, 2)).Value = vArray
    '// Delete blanks
    Sheet2.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

FORMULA

If you really need a formula take a look here:

Remove Blanks - Array Formula

Solution 2

Why not just use a filter where you exclude blanks from the appropriate column(s)?

Alternatively, you could create an additional flag column which designates each row to be included based on your blank criteria (eg, IF(OR(X="",Y=""),0,1)) and use it to filter your data.

Solution 3

I like Chris's advice. This is a vba approach. Try it on a copy of your file.

Sub delete_empty_rows()
Dim last As Long, i As Long
last = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = last To 1 Step -1
    If Application.CountA(Range("A" & i).EntireRow) = 0 Then
        Range("A" & i).EntireRow.Delete
    End If
Next i
End Sub