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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Legal Tender

New Member
Joined
May 9, 2014
Messages
21
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,441
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top