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

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

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

--

Dave Peterson

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

"Ivan" wrote in message
...
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

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

Ivan wrote:

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

"Ivan" wrote in message
...
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





--

Dave Peterson

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

Ivan

"Dave Peterson" wrote in message
...
Because a range with an address like "1:17" means rows. Not columns.

Ivan wrote:

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

"Ivan" wrote in message
...
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





--

Dave Peterson