Need a Macro to remove "blank lines"

[Traderdave]

I have a spreadsheet that contains over 80,000 lines of data. The lines of data are seperated by a repeated column heading on the page breaks, as well as 2 or 3 lines, (it varies), in between the necessary information. I need to figure out how to remove the column headings, (of which there are approximately 10,000 lines), as well as the blank lines between the data. I can not sort on a specific line and then delete the unnecessary data as when I do this cell "A2" loses its relationship with Cells "C2", "C3" and "C4". is there a Macro, ( or another easy way), to do this?

 

[pboltonchina]

Try this on a copy of your data. Change 'Your column header' in the code to the actual text in the header rows you want to get rid of. Also change the 3 in the statement 'For i = LR To 3 Step -1' to whatever number of rows you want to keep at the top of your sheet

Sub Delete()
Application.ScreenUpdating = False
      Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
    If Range("A" & i).Value = "Your column header" Or Range("A" & i).Value = "" Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub