-
Mar 1st, 2011, 03:48 PM
#1
[RESOLVED] [Excel VBA] Run time error 1004
I have some Excel spreadsheets with macros that I use to add and remove subtotals rows. The RemoveSubtotals macro works on some sheets but not on others. This happens in both Excel 2003 and Excel 2007. I want to remove all rows that start with "Subtotal" in column A on rows 2 through end of data.
The error I get (sometimes) is:
run time error 1004 - cannot use that command on overlapping selections
There are no merged cells in the spreadsheets.
The code I am using is below:
Code:
Sub RemoveSubtotals()
Dim rRange As Range
Dim strCriteria As String
Dim lngColumn As Long
Dim rHeaderCol As Range
Dim xlCalc As XlCalculation
Dim intErrors As Integer
Dim intTemp1 As Integer
Dim lngLastRowActiveSheet As Long
strCriteria = "Subtotal"
lngColumn = 1
intErrors = 0
If ActiveSheet.Cells(1, 18) <> "" Then intErrors = 1
If ActiveSheet.Cells(1, 19) <> "" Then intErrors = 2
' Store current Calculation then switch to manual.
' Turn off events and screen updating
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
' Remove any filters
ActiveSheet.AutoFilterMode = False
' Find last row in Column A with content, use the previous row as end of sort range (omit "Records Printed:" row).
' intLastRow = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
' If you want to also omit the last row from the hide function (based on Column A) use instead
lngLastRowActiveSheet = 0
If IsEmpty(ActiveSheet.Cells(1, 1)) Then
Exit Sub
Else
lngLastRowActiveSheet = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
End If
Set rRange = Range("A1:Z" & CStr(lngLastRowActiveSheet))
With rRange ' Filter, offset (to exclude headers) and delete visible rows
.AutoFilter Field:=lngColumn, Criteria1:=strCriteria
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete ' *** This is the line that causes the error!
End With
' Remove any filters
ActiveSheet.AutoFilterMode = False
' Clear Subtotal column headings
For intTemp1 = 19 To 29
ActiveSheet.Cells(1, intTemp1) = ""
ActiveSheet.Cells(1, intTemp1).Interior.ColorIndex = -4142
Next intTemp1
' Revert back
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
' Restore original filtering
If intErrors = 0 Then
Range("A1:Q1").Select
Else
If intErrors = 1 Then
Range("A1:R1").Select
Else
If intErrors = 2 Then
Range("A1:Z1").Select
End If
End If
End If
Selection.AutoFilter
ActiveSheet.Cells(1, 17 + intErrors).Select
End Sub
Any ideas as to the cause of this error would be appreciated. I have another method that could be used to remove the Subtotals lines, but the method shown here is much faster, when it works.
Thanks.
-
Mar 2nd, 2011, 04:13 AM
#2
Re: [Excel VBA] Run time error 1004
can you post a workbook with some sample data that demonstrates the problem?
post 2003 format and zip before attaching
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Mar 2nd, 2011, 11:57 AM
#3
Re: [Excel VBA] Run time error 1004
Ok, in order to post a sample spreadsheet where the macro fails, I needed to remove data from it that I don't want others to be able to see. I started by removing all data from rows 2 through end-of-data and columns 2 through 26, and I replaced all cell contents in column 1 that did not say Subtotal with a fixed value. The macro still returned the same error even with nearly all of the data removed. However, I still needed to remove the hidden data from the file which might identify where it came from. To do that, I had two options - use the add-in for Excel 2003 (which I did not have installed) or convert it to Excel 2007 and use the built-in tool for that. I loaded the spreadsheet into Excel 2007 and used the process from Microsoft's KB article to remove the hidden data, then saved the sheet as a .xlm file. Before zipping the file to upload, I tested one last time to see if the macro would still fail, and this time it worked. So, there is some hidden data somewhere that is causing the issue. Now I just have to try to locate which hiddden data is causing the problem, and see if it is something I can remove without too much trouble. If that is not possible, I will have to add error checking to the macro and if the error occurs, use the slower method to remove the Subtotal lines.
-
Mar 2nd, 2011, 12:19 PM
#4
Re: [Excel VBA] Run time error 1004
The mystery is solved. There were some hidden columns of data that I had not noticed, and they were causing the problem. I unhid them and the macro runs. Now I just need to add some code to the macro to unhide any hidden columns and that should take care of the issue.
-
Mar 2nd, 2011, 03:17 PM
#5
Re: [RESOLVED] [Excel VBA] Run time error 1004
glad it all works for you
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|