Deletion of blank rows above header row

[Papi]

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?

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

 

[Legal Tender]

Try this:

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]

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:

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.