Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I delete rows programmatically based on certain criteria? | New Users to Excel | |||
How can I delete rows programmatically based on certain criteria? | Excel Worksheet Functions | |||
How can I delete rows programmatically based on certain criteria? | Excel Programming | |||
How to delete programmatically a pivotcache? | Excel Programming | |||
delete row programmatically | Excel Programming |