Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for a Macro to erase particular rows in a given pattern
I need a Macro to erase particular rows in a given pattern, but I can't find one on the web. We are testing a new data collector and the raw data can be opened in Excel. The problem is, we have 30000+ rows of data, but we only need rows 1, 5, 9, 13, 17, 21 and so on... deleting every three rows manually is taking forever. I have to say I'm not used to the macros, but I think I can figure out how to run one using the spreadsheet. Can someone give me any suggetsions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for a Macro to erase particular rows in a given pattern
as long as you have at least one blank column to the right of your data
range, one quick way to do it is like this: Enter the formula: =IF(MOD(ROW(A1)-1,4)=0,0,1) This formula will evaulate to 0 for all of the rows that you want to keep, and to 1 for all of the rows that you want to delete. Sort by that formula, delete all of the rows that have a 1 in that column. I mean do all that in code, not suggesting manually. The only time this approach would fail is if the furthest right column in the spreadsheet has something already in it, so be aware of that. "Quco" wrote: I need a Macro to erase particular rows in a given pattern, but I can't find one on the web. We are testing a new data collector and the raw data can be opened in Excel. The problem is, we have 30000+ rows of data, but we only need rows 1, 5, 9, 13, 17, 21 and so on... deleting every three rows manually is taking forever. I have to say I'm not used to the macros, but I think I can figure out how to run one using the spreadsheet. Can someone give me any suggetsions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for a Macro to erase particular rows in a given pattern
The key is to work from bottom up. Try this idea
for i= cells(rows.count,"a").end(xlup).row to 2 step -3 rows(i).delete next i -- Don Guillett SalesAid Software "Quco" wrote in message ... I need a Macro to erase particular rows in a given pattern, but I can't find one on the web. We are testing a new data collector and the raw data can be opened in Excel. The problem is, we have 30000+ rows of data, but we only need rows 1, 5, 9, 13, 17, 21 and so on... deleting every three rows manually is taking forever. I have to say I'm not used to the macros, but I think I can figure out how to run one using the spreadsheet. Can someone give me any suggetsions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows in A Pattern | Excel Discussion (Misc queries) | |||
Deleting Rows in a Pattern | Excel Discussion (Misc queries) | |||
Deleting Rows In A Pattern | Excel Discussion (Misc queries) | |||
Erase duplicate rows | Excel Discussion (Misc queries) | |||
Can a macro erase it's self after a set criteria? | Excel Worksheet Functions |