Runtime error 1004
 

Hello,
I have a macro that imports a text file. There is something in the text
file that is throwing error 1004 and I am not sure exactly what it is.
The code is:
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).EntireR ow.Delete
End Sub
The row that is causing the error is:
Selection.EntireRow.SpecialCells(xlBlanks).EntireR ow.Delete
The error that I get is Run-Time error 1004, cannot use that command on
overlapping selections. I do not see where I am overlapping any
sections. Can anyone help with this? Thanks Terry

Runtime error 1004
 

Select A1 and then hold Ctrl down and click on C1. If you then run code
like this:

Selection.EntireRow.Delete

you get your run time error about an overlapping selection. The overlap is
the entire row of A1 and the entire row of C1. They are the same row.

As far as I can tell, what you are trying to do is delete any row that has
at least one empty cell. You can't do it your way unfortunately. Here is
an way that should work. It depends on the entire block being selected when
it is run.

Sub DelAnyRowWithABlankInSelection()
Dim NewRg As Range
Dim CurrRow As Range
Dim BlankCells As Range
On Error GoTo NoBlanks
For Each CurrRow In Selection.Rows
Set BlankCells = CurrRow.SpecialCells(xlCellTypeBlanks)
If NewRg Is Nothing Then
Set NewRg = CurrRow
Else
Set NewRg = Union(NewRg, CurrRow)
End If
Nextrow:
Next
If Not NewRg Is Nothing Then NewRg.Delete (xlShiftUp)
Exit Sub
NoBlanks:
Resume Nextrow
End Sub


--
Jim
"Terry K" wrote in message
oups.com...
| Hello,
| I have a macro that imports a text file. There is something in the text
| file that is throwing error 1004 and I am not sure exactly what it is.
| The code is:
| 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).EntireR ow.Delete
| End Sub
| The row that is causing the error is:
| Selection.EntireRow.SpecialCells(xlBlanks).EntireR ow.Delete
| The error that I get is Run-Time error 1004, cannot use that command on
| overlapping selections. I do not see where I am overlapping any
| sections. Can anyone help with this? Thanks Terry
|

Runtime error 1004
 

Thank you very much Jim. Problem solved.
Terry