Delete summary rows programmatically
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" wrote:
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.
|