A bug : Run-time error '1004'
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 |
A bug : Run-time error '1004'
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 |
A bug : Run-time error '1004'
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 |
A bug : Run-time error '1004'
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 |
A bug : Run-time error '1004'
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 |
A bug : Run-time error '1004'
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 |
All times are GMT +1. The time now is 05:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com