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

"Ivan" wrote:

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
-------------------------

but I'm still curious, why my first code wasn't successful?

Ivan