Generating run tim error

[jtkinsella]

I am wondering if there is anyone that has run into this before. Here
is some code that is in one of my macros.
Sub terr_100()
ChDir "\\dynamics\Documents\Terry\EOD_REPORTS"
Workbooks.OpenText Filename:= _
"\\dynamics\Documents\Terry\EOD_REPORTS\100_daily_ar.txt",
Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(14, 1), Array _
(51, 1), Array(65, 1), Array(78, 1), Array(91, 1), Array(104,
1), Array(117, 1)), _
TrailingMinusNumbers:=True
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Windows("100_daily_ar.txt").Activate
Sheets("100_daily_ar").Select
Sheets("100_daily_ar").Move after:=Workbooks("all
a.r.xls").Sheets(1)
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End Sub
The line "Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete"
is throwing error 1004 "cannot use that command on overlapping
selections". There is something in the text file that causes this some
days, other days it is fine. I am using this same line of code in some
other marcos without a problem. I have tried to look for a solution but
have been unable to come up with any. Would anyone be able to let me
know a way around this? Right now what I am doing is running the macro
and if it throws the error I debug, sort the list and remove the blank
rows myself, move the macro to the next line and allow it to continue
on. I was hoping to have this run as a scheduled task so I do not
really want to get into error handling. Any suggestions or has anyone
seen this before?
Thanks Terry

 

[Jim Cone]

Terry,
You might be able to work around this by checking for blanks
in only one column...
Sheets("100_daily_ar").Columns("A").SpecialCells(xlBlanks).EntireRow.Delete

Jim Cone
San Francisco, USA


<[email protected]>
wrote in message I am wondering if there is anyone that has run into this before. Here
is some code that is in one of my macros.
Sub terr_100()
ChDir "\\dynamics\Documents\Terry\EOD_REPORTS"
Workbooks.OpenText Filename:= _
"\\dynamics\Documents\Terry\EOD_REPORTS\100_daily_ar.txt",
Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(14, 1), Array _
(51, 1), Array(65, 1), Array(78, 1), Array(91, 1), Array(104,
1), Array(117, 1)), _
TrailingMinusNumbers:=True
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Windows("100_daily_ar.txt").Activate
Sheets("100_daily_ar").Select
Sheets("100_daily_ar").Move after:=Workbooks("all
a.r.xls").Sheets(1)
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End Sub
The line "Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete"
is throwing error 1004 "cannot use that command on overlapping
selections". There is something in the text file that causes this some
days, other days it is fine. I am using this same line of code in some
other marcos without a problem. I have tried to look for a solution but
have been unable to come up with any. Would anyone be able to let me
know a way around this? Right now what I am doing is running the macro
and if it throws the error I debug, sort the list and remove the blank
rows myself, move the macro to the next line and allow it to continue
on. I was hoping to have this run as a scheduled task so I do not
really want to get into error handling. Any suggestions or has anyone
seen this before?
Thanks Terry