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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For/Next using Thing
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For/Next using Thing
Lindy,
The problem is that when you delete row 5 say, the row in focus is then what was row 6 (as it moves up to take row 5's position). Next time through the loop will look at the NEW row 6, so the old row 6 gets skipped over, which is a problem if it should be deleted. As Don says, work bottom up, and assuming you have data in column A use For i = Cells(Rows.Count,"A").End(xlUp).Row To 1 Step -1 If Cells(i,"A").value = "" then Cells(i,"A")..entirerow.delete Else Cells(i,"A")..font.bold = true End If Next i -- HTH RP (remove nothere from the email address if mailing direct) "Lindy" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For/Next using Thing
Thanks Bob and Don for the input. Take care.
-----Original Message----- Lindy, The problem is that when you delete row 5 say, the row in focus is then what was row 6 (as it moves up to take row 5's position). Next time through the loop will look at the NEW row 6, so the old row 6 gets skipped over, which is a problem if it should be deleted. As Don says, work bottom up, and assuming you have data in column A use For i = Cells(Rows.Count,"A").End(xlUp).Row To 1 Step -1 If Cells(i,"A").value = "" then Cells(i,"A")..entirerow.delete Else Cells(i,"A")..font.bold = true End If Next i -- HTH RP (remove nothere from the email address if mailing direct) "Lindy" wrote in message ... 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. . |
Reply |
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 |