Delete summary rows programmatically

[Brian]

Access developer here with what is probably an age-old question regarding
some Excel manipulation.

I get a spreadsheet periodically that has header information (consistently
13 lines), followed by good flat-file-type data (number of lines varies),
followed by total information (two lines). I need to strip out the header &
total rows so I can import the normalized data into Access.

I wrote an Auto_Open macro in another spreadsheet that opens this one,
removes the top 13 lines, Home, Ctrl-DownArrow to get to the last populated
row, select the row, up arrow once, deletes the two rows, then saves the file
as a CSV for import into my Access table for long-term storage & analysis.

The header removal is easy, because I just remove the first 13 lines. When I
record a macro using Ctrl-ArrowDown to get to the end and then delete the
last two lines, however, the macro stores the row numbers, but I need the row
numbers to be relative to the last populated row, not a specific number.

I know could just import them into Access and delete them there, but I would
prefer to strip them out in my macro or VBA first.

 

[Brian]

OK, here is what I have so far.

Workbooks.Open FileName:=MyFileName
Rows("1:14").Select
Selection.Delete Shift:=xlUp Selection.End(xlDown).Select
ActiveCell.EntireRow.Delete
Selection.End(xlUp).Select
ActiveCell.EntireRow.Delete

This does it, but I have just two small questions:

1. Would it be better to use a function to find the last row rather than
Selection.End(xlDown).Select?
2. How would I code the last four lines to select & delete both rows
togetherrather than deleting one row, moving up a row, and deleting the other
one?

 

[Brian]

Thank you.

I always like my code to be as lean as possible, and that is a definite
improvement.

 

[Rolf]

Brian
Try this code:

Selection.End(xlDown).Select
Range(ActiveCell, Selection.Offset(-1, 0)).EntireRow.Delete

Rolf