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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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