Trouble deleting multiple columns?

John.McLaughlin

Board Regular
Joined
Jul 19, 2011
Messages
150
The macro appears to delete all the columns, but stops the macro at the Column delete command?
(Run Time Error '1004. Delete method of range class failed)

Code:
Sub RouteSS()

    Workbooks("SCHEDULED.xlsm").Activate
    ActiveWorkbook.Save
    Sheets("SCHEDULED").Visible = True
    Sheets("SCHEDULED").Select
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "C:\data\Business\route\SCHEDULED.xlsm"
    ActiveWorkbook.Close
   
   
' Open New Route Only Workbook
    Workbooks.Open Filename:="C:\data\Business\route\SCHEDULED.xlsm"
   
    Application.DisplayAlerts = False

    Sheets("BUILDTHIS").Select
    ActiveWindow.SelectedSheets.Delete

    Sheets("Sales Meeting").Select
    ActiveWindow.SelectedSheets.Delete

    Sheets("To Do").Select
    ActiveWindow.SelectedSheets.Delete
   
    Sheets("To Do 2").Select
    ActiveWindow.SelectedSheets.Delete

    Sheets("To Do 3").Select
    ActiveWindow.SelectedSheets.Delete

    Sheets("TASKS").Select
    ActiveWindow.SelectedSheets.Delete

    Sheets("PLANS").Select
    ActiveWindow.SelectedSheets.Delete
   
    Application.DisplayAlerts = True

    Workbooks("SCHEDULED.xlsm").Activate
    Sheets("SCHEDULED").Visible = True
    Sheets("SCHEDULED").Select
    ActiveSheet.Buttons.Delete
  
    Range("H1:H37").UnMerge
    Range("A1:Z1").Clear



  Range("$C:$H").Select

' Below line is the Yellow highlighted code
Range("$C:$H").Delete

    Rows("1:2").Select
   
    Range("A1:B1").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Sheets("SCHEDULED").Select
    Range("A2").Select
    ActiveWindow.Zoom = 200
   
   
    ActiveWorkbook.Save
    ActiveWorkbook.Close


' Open scheduled workbook
    Workbooks.Open Filename:="C:\data\SCHEDULED.xlsm"
    ChDir "c:\"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Sheets("SCHEDULED").Visible = True
    Sheets("SCHEDULED").Select
   
End Sub


Backstory: This is my daily "planner" workbook, it is copied to my tablet each morning. The macro first deletes unnecessary worksheets, and then deletes rows and columns on the remaining worksheet. The workbook is opened automatically when I start Excel on my tablet. The worksheet displays a chronologically sorted list of customers, whose names I can then touch and open their workbook.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
That can happen if the sheet is protected, if it is you need to unprotect it first.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
Anything like a pivot table in the range being deleted?

Other than being protected, there is no obvious reason for the error to occur. Trying to delete part of a pivot table would cause an error but I don't know if it is the same error without setting up a test file so that is only a guess.
 

John.McLaughlin

Board Regular
Joined
Jul 19, 2011
Messages
150
Anything like a pivot table in the range being deleted?

Other than being protected, there is no obvious reason for the error to occur. Trying to delete part of a pivot table would cause an error but I don't know if it is the same error without setting up a test file so that is only a guess.

No pivot table, I gave up and just cleared the range instead

 

Watch MrExcel Video

Forum statistics

Threads
1,127,331
Messages
5,624,057
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top