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.

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.

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" <(E-Mail Removed)> wrote in message
> news:FF96396A-1722-4987-98AC-(E-Mail Removed)...
> > 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.
>
>
>

Brian
Try this code:

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

Rolf

"Brian" <(E-Mail Removed)> wrote in message
news:FF96396A-1722-4987-98AC-(E-Mail Removed)...
> 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.