Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For/Next using Thing
I have a spreadsheet with 40,000 records. The original
file is a .txt file that is formatted with page breaks: 13 rows for each page header, and then 25 rows/records of actual data, 13 rows of page header, then 25 rows/records of data, etc. I am opening this file in MS Excel and performing analysis. The first step in my macro is to delete all page header rows and then delete all rows with no data so that I end up with a table that contains only my data records. Column A is blank for all rows that contain column heading information. I am using a For/Next statement to do this. It looks like this: Note: My selection is all rows in column A of the spreadsheet Sub DeleteHeader() For Each thing In Selection If thing.value = "" then thing.entirerow.delete Else thing.font.bold = true End If Next thing End Sub Here is my problem: if I have two consecutive rows that have blank data in column A, it deletes the first row, but then skips the next row. Essentially, if thing is row#2 and it is blank, it deletes row#2 and moves all data in the file up one record (so what was row#3 is now row#2). Since the next value of thing is row#3, it skips what is now row#2 and does not delete it if it is blank. As a result, I am having to run through this Subroutine at least twice to delete all blank records. How can I have this routine recognize that if a row is deleted that the value of "thing" needs to stay at its present value? I am unable to do any sorts since I need the data records to remain in the original sequence. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One Last Thing | Excel Discussion (Misc queries) | |||
Is there such a thing... | Excel Discussion (Misc queries) | |||
Doing it's own thing | Excel Programming | |||
Another thing | Excel Programming | |||
oh, and another thing | Excel Programming |