I have a workbook with some macros that have worked really well for a while now until recently that they started showing Error 1004: That command cannot be used on multiple selections. After some brainstorming, I found out that some hidden columns and rows.
Problem: Error 1004: That command cannot be used on multiple selections.
When: Running macro that deletes selected rows or inserts rows.
Possible cause: Filtered rows and/or columns
The InsertRows module inserts an X amount of rows, depending on the number the users gives (splitVal) and copies everything, formulas and format, from the original keycell row.
Sub InsertRows(ByVal splitVal As Integer, ByVal keyCells As Range, ws As Worksheet)
On Error GoTo ErrorHandler
PW
ws.Unprotect Password
ws.DisplayPageBreaks = False
WBFast
With keyCells
.Offset(1).Resize(splitVal).EntireRow.Insert
.EntireRow.Copy .Offset(1, 0).Resize(splitVal).EntireRow 'Error happens here
End With
ExitHandler:
ws.Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
Exit Sub
ErrorHandler:
WBNorm
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Insert_Rows, line " & Erl & "."
GoTo ExitHandler
End Sub
-
The delete table rows module, deletes the rows in a table that where selected by the user. Resulting range to be deleted will, in most cases, have filtered rows and there might be filtered columns. The error happens when it gets to the deleting part, same error as above.
Sub DeleteTableRows()
'PURPOSE: Delete table row based on user's selection
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Call PW
Dim rng As Range
Dim DeleteRng As Range
Dim cell As Range
Dim TempRng As Range
Dim Answer As Variant
Dim area As Range
Dim ReProtect As Boolean
Dim copyRange As Range
Dim pasteRange As Range
Dim wb As Workbook
Dim a As Long
WBFast
'Set Range Variable
On Error GoTo InvalidSelection
Set rng = Selection
On Error GoTo 0
'Unprotect Worksheet
With ThisWorkbook.ActiveSheet
If .ProtectContents Or ProtectDrawingObjects Or ProtectScenarios Then
On Error GoTo InvalidPassword
.Unprotect Password
ReProtect = True
On Error GoTo 0
End If
End With
Set wb = ThisWorkbook
'Loop Through each Area in Selection
For Each area In rng.Areas
For Each cell In area.Cells.Columns(1)
'Is selected Cell within a table?
InsideTable = True
'Gather rows to delete
If InsideTable Then
On Error GoTo InvalidActiveCell
Set TempRng = Intersect(cell.EntireRow, ActiveCell.ListObject.DataBodyRange)
On Error GoTo 0
If DeleteRng Is Nothing Then
Set DeleteRng = TempRng
Else
Set DeleteRng = Union(TempRng, DeleteRng)
End If
End If
Next cell
Next area
'Error Handling
If DeleteRng Is Nothing Then GoTo InvalidSelection
If DeleteRng.Address = ActiveCell.ListObject.DataBodyRange.Address Then GoTo DeleteAllRows
If ActiveCell.ListObject.DataBodyRange.Rows.Count = 1 Then GoTo DeleteOnlyRow
'Ask User To confirm delete (since this cannot be undone)
DeleteRng.Select
If DeleteRng.Rows.Count = 1 And DeleteRng.Areas.Count = 1 Then
Answer = MsgBox("Are you sure you want to delete the currently selected table row? " & _
" This cannot be undone...", vbYesNo, "Delete Row?")
Else
Answer = MsgBox("Are you sure you want to delete the currently selected table rows? " & _
" This cannot be undone...", vbYesNo, "Delete Rows?")
End If
'Delete row (if wanted)
If Answer = vbYes Then
'Error 1004 happens here
For a = DeleteRng.Areas.Count To 1 Step -1
Debug.Print DeleteRng.Areas.Count
DeleteRng.Areas(a).EntireRow.Delete
Next a
WBNorm
End If
'Protect Worksheet
If ReProtect = True Then wb.Worksheets("Open Orders").Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
Exit Sub
'ERROR HANDLERS
InvalidActiveCell:
MsgBox "The first cell you select must be inside an Excel Table. " & _
"The first cell you selected was cell " & ActiveCell.Address, vbCritical, "Invalid Selection!"
If ReProtect = True Then wb.Worksheets("Open Orders").Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
WBNorm
Exit Sub
InvalidSelection:
MsgBox "You must select a cell within an Excel table", vbCritical, "Invalid Selection!"
If ReProtect = True Then wb.Worksheets("Open Orders").Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
WBNorm
Exit Sub
DeleteAllRows:
MsgBox "You cannot delete all the rows in the table. " & _
"You must leave at least one row existing in a table", vbCritical, "Cannot Delete!"
If ReProtect = True Then wb.Worksheets("Open Orders").Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
WBNorm
Exit Sub
DeleteOnlyRow:
MsgBox "You cannot delete the only row in the table.", vbCritical, "Cannot Delete!"
If ReProtect = True Then wb.Worksheets("Open Orders").Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
WBNorm
Exit Sub
InvalidPassword:
MsgBox "Failed to unlock password with the following password: " & Password
If ReProtect = True Then wb.Worksheets("Open Orders").Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowFormattingCells:=True
WBNorm
Exit Sub
End Sub
-
Sub WBFast()
With ThisWorkbook.Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
End Sub
Sub WBNorm()
With ThisWorkbook.Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
I have really tried my best in coming with an efficient way of dealing with this, and the code worked until recently, that I saw a sudden need for the users to hide columns. What must I do, to do what I want even if there are rows and columns hidden/filtered, resulting in noncontinuous ranges?
Unhiding and unfiltering is out of question. The user can set complex filters and hide lots of columns he/she doesn't need, I would like to keep those things and not taking them away.
The part about saving filters and then reapply them, I tried this macro: In Excel VBA, how do I save / restore a user-defined filter? but I could not make it work.
Is there truly no way of deleting rows in non contiguous ranges?
