Results 1 to 5 of 5

Thread: [RESOLVED] [Excel VBA] Run time error 1004

  1. #1

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Resolved [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.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    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.

  4. #4

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    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.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width