How to pastes row of data into another sheet while removing blank lines?

[Mattyastill]

Hi,

I've used conditional formatting on a large set of data to highlight a specific criteria. I have then got formula which gets the highlighted rows into a separate table but the gaps are still there. I was wondering if i was possible to use formula to paste in the rows into another sheet but get rids of the blank lines.

I will be continually posting new sets of data and into the master template i've made to highlight certain areas. Having the ability to copy and paste the dataset and it automatically display the Rows from the conditional formatting on a new sheet would be great.

Any help would be greatly appreciated.

Thanks.

 

[cooper645]

Not aware of any way to do it with a formula, but you could use a macro like the following;

Sub DelRows()Dim i As Long, LastRow As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1 'last row of data in column A to 1
If Cells(i, 1) = "" Then Cells(i, 1).EntireRow.Delete 'if A+row num is blank then delete row
Next i
Application.ScreenUpdating = True
MsgBox "Blank rows deleted"
End Sub