Consolidating non-blank rows from a 2d range

[daabs]

I have a 2d range which has some blank rows in it. I would like to be able to take the non-blank rows and consolidate them as a contiguous block in another range without disturbing the original table.

Any ideas, anyone?

 

[crimson_b1ade]

you can try this. there's no copy to column procedure (since you didn't mention where it was to be copied). so, you can copy the data to the column of your choosing the run the below macro. note: the below macro uses defaults (ie. Sheet1, Column A) so you'll have to change those to the sheet you want and the column letter you want.

Sub DeleteBlanks()

Dim LR As Long, i As Long

LR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 1 Step -1
    If Range("A" & i) = "" Then
       Range("A" & i).Delete Shift:=xlUp
    End If
Next

End Sub

 

[daabs]

thanks Blade. problem solved.