Deletion of blank rows above header row

Papi

Well-known Member
Joined
May 22, 2007
Messages
1,592
The line below was working great as part of a macro pulling data from another file. The file has changed a few times lately where it is not always the 14 rows that can be deleted eg. this morning it only has 12 rows and the day before 11. What would be needed to have the code go down in column B and once finding the first data then to delete the rows above that eg. delete rows 1-12?

Code:
    Rows("1:14").EntireRow.Delete
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Legal Tender

New Member
Joined
May 9, 2014
Messages
19
Try this:

Code:
Sub Delete_Headers()
    Range("B1").Select
    If ActiveCell.Value = "" Then
    Selection.End(xlDown).Select
    Rows("1:" & ActiveCell.Row - 1).Delete shift:=xlUp
    End If
End Sub

It has an if test in case cell B1 is not blank (in which case we would do nothing as row 1 would be the header row). If B1 was blank then go down to the first non blank and delete all rows from row 1 to the row above. Just to be clear this should probably only be your solution if you are sure that the first non blank in column B will also represent the first row with data, otherwise you could be deleting data that was found in other columns.

Hope that helps.
 

Papi

Well-known Member
Joined
May 22, 2007
Messages
1,592
Thanks very much Legal Tender. It works perfect. I reviewed a bunch of history files and column B will always have data that can be relied on. I appreciate your very quick and accurate solution.


Try this:

Code:
Sub Delete_Headers()
    Range("B1").Select
    If ActiveCell.Value = "" Then
    Selection.End(xlDown).Select
    Rows("1:" & ActiveCell.Row - 1).Delete shift:=xlUp
    End If
End Sub

It has an if test in case cell B1 is not blank (in which case we would do nothing as row 1 would be the header row). If B1 was blank then go down to the first non blank and delete all rows from row 1 to the row above. Just to be clear this should probably only be your solution if you are sure that the first non blank in column B will also represent the first row with data, otherwise you could be deleting data that was found in other columns.

Hope that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top