Condense a range to exclude blank rows

[Bluzdude]

I have a large range of cells. Some of the rows within this range contain all blank cells. I need a macro that will identify the blank rows and move the next row that contains values up to the last row that contained values, thus, condensing or compressing the range to include only rows that contain values. Each row will have a column that, when blank, indicates that the rest of the cells on that row are also blank.

I don't want to delete entire rows from the worksheet because that would remove data in other locations on the rows.

I assume the macro would have to do something like stepping through each row in the range and then do a "select" and "move" for the rows below the last row containing values and continue to the bottom of the range.

Is this possible?

 

[Bluzdude]

Run this version. It loops up from the bottom of the table.

Sub NoBlanks_2()
    Dim c As Range
    Dim Rw As Long, RwLast As Long
    
    RwLast = Range("A65536").End(xlUp).Row
    
    For Each c In Range("A:B").SpecialCells(xlCellTypeConstants)
        If c.Value = "" Then c.ClearContents
    Next c
    For Rw = RwLast To 2 Step -1
        If IsEmpty(Cells(Rw, 1)) Then
            Cells(Rw, 1).Resize(1, 2).Delete shift:=xlShiftUp
        End If
    Next Rw
End Sub

Denis

This code works like a charm!!!! Great work, Denis!!
Thanks for being so patient with me. This will be a terrific timesaver!!

 

[SydneyGeek]

You're welcome

Denis