XL2003 on WinXP

I have a macro that opens a large (fixed width delimited .txt) data file,
parses it into cells, removes some extra header rows, then removes blank
lines (every other line). The problem is that I'm losing lots of extra rows
when the macro runs. I've double-checked this part of the code by comparing
a debug msgbox (below) to the results I get when I manually manipulate the
data- a difference of over 7000 extra rows seem to dissapear when the macro
runs in my source workbook.

'-------------------------------------------------------------------------------
'-----------------------
'delete the extra header rows
'-----------------------
Sheet1.Activate
Sheet1.Rows("1:6").Select
Selection.Delete Shift:=xlUp
Sheet1.Rows("2:4").Select
Selection.Delete Shift:=xlUp
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

'-----------------------
'Delete all blank rows
'-----------------------
For i = LastRow To 1 Step -1
If Sheet1.Range("A" & Trim(Str(i))).Value = "" Then
Application.StatusBar = "Deleting blank row: " & Str(i)
Sheet1.Rows(i).Delete
End If
Next
LastRow2 = Cells(Cells.Rows.Count, "A").End(xlUp).Row

MsgBox "count #1 = " & LastRow & Chr(13) & Chr(13) & _
"count #2 = " & LastRow2
'-------------------------------------------------------------------------------

When run all together, the message box returns
count 1= 18234 (correct)
count 2= 1920 ** if only blank rows are deleted, this should be 9116! **

I visually confirmed that there are only 1920 rows of data after the macro
runs. When I import the raw data file manually, I get the 18000+ rows of
data, then I delete the 9 extra rows at the top and sort blank rows to the
bottom- I get 9116 rows of data, 7000+ more than my macro gave me.

I then pasted the two chunks of code above into a new workbook and ran them
separately on a fresh import of the raw data, and ended up with 9116...so
something weird is going on in the main workbook where I'm losing all the
extra rows.

Any ideas?

Many thanks,
Keith

You said every other line (row) is blank. You can use:

On Error Resume Next 'In case there are no blank cells
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
On Error GoTo 0

which will delete all rows in which the cell in column A is blank.

Alan

"ker_01" wrote:

XL2003 on WinXP

I have a macro that opens a large (fixed width delimited .txt) data file,
parses it into cells, removes some extra header rows, then removes blank
lines (every other line). The problem is that I'm losing lots of extra rows
when the macro runs. I've double-checked this part of the code by comparing
a debug msgbox (below) to the results I get when I manually manipulate the
data- a difference of over 7000 extra rows seem to dissapear when the macro
runs in my source workbook.

'-------------------------------------------------------------------------------
'-----------------------
'delete the extra header rows
'-----------------------
Sheet1.Activate
Sheet1.Rows("1:6").Select
Selection.Delete Shift:=xlUp
Sheet1.Rows("2:4").Select
Selection.Delete Shift:=xlUp
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

'-----------------------
'Delete all blank rows
'-----------------------
For i = LastRow To 1 Step -1
If Sheet1.Range("A" & Trim(Str(i))).Value = "" Then
Application.StatusBar = "Deleting blank row: " & Str(i)
Sheet1.Rows(i).Delete
End If
Next
LastRow2 = Cells(Cells.Rows.Count, "A").End(xlUp).Row

MsgBox "count #1 = " & LastRow & Chr(13) & Chr(13) & _
"count #2 = " & LastRow2
'-------------------------------------------------------------------------------

When run all together, the message box returns
count 1= 18234 (correct)
count 2= 1920 ** if only blank rows are deleted, this should be 9116! **

I visually confirmed that there are only 1920 rows of data after the macro
runs. When I import the raw data file manually, I get the 18000+ rows of
data, then I delete the 9 extra rows at the top and sort blank rows to the
bottom- I get 9116 rows of data, 7000+ more than my macro gave me.

I then pasted the two chunks of code above into a new workbook and ran them
separately on a fresh import of the raw data, and ended up with 9116...so
something weird is going on in the main workbook where I'm losing all the
extra rows.

Any ideas?

Many thanks,
Keith