For/Next using Thing

[Lindy]

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.

 

[Don Guillett]

go from the bottom up
for i = 1334 to 1 step -1
dddd
next i

 

[Bob Phillips]

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]

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)





.