A bug : Run-time error '1004'

[Ivan]

Hello,

I'm using Excel 2007 and with the help of a macro I wished to delete all
rows above and all columns left to the specific cell of my worksheet.
In the example for specific cell B4 the code is as follows:

------------------------------------------
Dim MyCol As Long, MyRow As Long
Dim ForDelete As Range
Dim MyAdress As String

MyCol = 2
MyRow = 4

' To delete rows

If MyRow > 1 Then
MyAdress = "1:" & (MyRow - 1)
Set ForDelete = ActiveSheet.Rows(MyAdress)
ForDelete.Delete
Set ForDelete = Nothing
End If

' To delete columns

If MyCol > 1 Then
MyAdress = "1:" & (MyCol - 1)
Set ForDelete = ActiveSheet.Columns(MyAdress)
ForDelete.Delete
Set ForDelete = Nothing
End If
------------------------------------------

It's weird that the first part of code for deletion of rows run
successfully, the equivalent code for deletion of columns but ended with the
error message:Run-time error '1004' Application-defined or object-defined
error !?

I have changed the problematic code with a bypass code:

-------------------------
If MyRow > 1 Then
For i = MyRow - 1 To 1 Step -1
ActiveSheet.Rows(i).Delete
Next i
End If

 

[Barb Reinhardt]

I usually do something like this

Set ForDelete = Nothing
If MyCol > 1 Then
MyAdress = "1:" & (MyCol - 1)
if ForDelete is nothing then
Set ForDelete = ActiveSheet.Columns(MyAdress)
else
Set ForDelete = Union(ForDelete, ActiveSheet.Columns(MyAdress))
end if

End If

'Once you've gathered everything you want to delete, then delete it all.
If not ForDelete is nothing then
ForDelete.Delete
End if

 

[Dave Peterson]

Another:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRow As Long
Dim myCol As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1") 'or activesheet

With wks
Set myCell = .Range("B4")

myRow = myCell.Row
myCol = myCell.Column

If myRow > 1 Then
.Range("a1", .Cells(myRow - 1, "A")).EntireRow.Delete
End If
If myCol > 1 Then
.Range("a1", .Cells(1, myCol - 1)).EntireColumn.Delete
End If
End With
End Sub

 

[Ivan]

Maybe I was not enough clear in my previous question. The question is: How
is it possible that the equivalent code, which is in original version OK for
rows, is not OK for columns?

Ivan

 

[Dave Peterson]

Because a range with an address like "1:17" means rows. Not columns.

 

[Ivan]

Thank you Dave (I 'm blushing with shame).

Ivan

Because a range with an address like "1:17" means rows. Not columns.