Delete summary rows programmatically
 

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.

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.

Delete summary rows programmatically
 

Try this routine:

Dim LRow As Long

Workbooks.Open Filename:=MyFileName
Rows("1:14").Delete
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & (LRow - 1) & ":A" & LRow).EntireRow.Delete

Mike F
"Brian" wrote in message
...
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.

Delete summary rows programmatically
 

Thank you.

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

"Mike Fogleman" wrote:

Try this routine:

Dim LRow As Long

Workbooks.Open Filename:=MyFileName
Rows("1:14").Delete
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & (LRow - 1) & ":A" & LRow).EntireRow.Delete

Mike F
"Brian" wrote in message
...
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.

Delete summary rows programmatically
 

Brian
Try this code:

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

Rolf

"Brian" wrote in message
...
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.