1

I need to delete selected rows in a template.

Selection.EntireRow.Delete handles all my scenarios, but one.

When I select the same row, in multiple selections Selection.EntireRow.Delete will throw a 1004 Error.

I've written an ErrorHandler to warn about the situation, and then resume to not leave the template unlocked.

Sub DelRows()

    Dim answer As VbMsgBoxResult, LRow As Integer
    LRow = Range("D5").Value
    
    If Selection.Cells(1, 1).Row < 9 Or Selection.Cells(1, 1).Row + Selection.Rows.Count > LRow - 1 Then                                            
        MsgBox "Forbidden selection."
    ElseIf Selection.Rows.Count > LRow - 11 Then                                                                                                    
        MsgBox "Can't delete all rows."
    Else
        On Error GoTo ErrHandler
        ActiveSheet.Unprotect Password:="xx"
        answer = MsgBox("Are you sure you want to delete all rows?", vbOKCancel + vbExclamation, "Warning")
        If answer = 1 Then Selection.EntireRow.Delete                        
        Rows((LRow + 2) & ":1048576").EntireRow.Hidden = True                               
        ActiveSheet.Protect Password:="xx", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFiltering:=True
    
    End If
    Exit Sub

    ErrHandler:
        If Err.Number = 1004 Then
            MsgBox "Can't select the same row twice: " & Err.Description
            Resume Next
        Else
            MsgBox Err.Description
            Resume Next
    End If
End Sub

The thing works, but my experience with error handling isn't the best.

Can I avoid this error with some other method?
Can I check the selection for multiple selections in the same row, or isn't it worth the effort of doing so?

I could probably loop all selected cells, create an array containing the rows for each area and then compare the results, but that seems excessive.

--Final solution--

Thanks to Pᴇʜ I managed to not only solve the problem, but find and solve two more at the same time.

Multiple selections break my Selection.Cells(1, 1).Row and Selection.Rows.Count as they only care about the first area. Which can both be fixed by looping the areas.

    Dim answer As VbMsgBoxResult, LRow As Long, i As Long, RowCount As Long, TopRow As Long
    LRow = Range("D5").Value
    TopRow = Selection.Cells(1, 1).Row
    
    For i = 1 To Selection.Areas.Count
        RowCount = RowCount + Selection.Areas(i).Rows.Count
        If Selection.Areas(i).Cells(1, 1).Row < TopRow Then TopRow = Selection.Areas(i).Cells(1, 1).Row
    Next i
    
    If TopRow < 9 Or TopRow + RowCount > LRow - 1 Then                                         
        MsgBox "Forbidden selection."
    ElseIf RowCount > LRow - 11 Then                                                                                                    
        MsgBox "Can't delete all rows."
    Else
        ActiveSheet.Unprotect Password:="xx"
        answer = MsgBox("Are you sure you want to delete all rows?", vbOKCancel + vbExclamation, "Warning")
        If answer = 1 Then Selection.EntireRow.Delete                        
        Rows((LRow + 2) & ":1048576").EntireRow.Hidden = True                               
        ActiveSheet.Protect Password:="xx", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFiltering:=True
    
    End If
End sub
Community
  • 1
  • 1
Christofer Weber
  • 1,464
  • 1
  • 9
  • 18
  • 2
    Note that you must declare `LRow As Long`. Excel has more rows than `Integer` can handle! I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. – Pᴇʜ Dec 03 '18 at 15:24
  • Good call. I've been slightly afraid of Long since I had to convert a few projects from 32bit to 64bit that used LongLong. I see now that this is not a problem and not the same thing. I'll stick to long in the future, thanks. – Christofer Weber Dec 03 '18 at 15:41

2 Answers2

3

You can do a trick and use the Application.Union method

Union(Selection.EntireRow, Selection).delete

This avoids the error of multiple selections in one row.

Edit according to comments:

Dim i As Long, RowCount As Long
For i = 1 To Selection.Areas.Count
    RowCount = RowCount + Selection.Areas(i).Rows.Count
Next i
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Exactly the thing I was looking for. Works great! – Christofer Weber Dec 03 '18 at 15:44
  • I noticed that my `Selection.Rows.Count` doesn't work with multiple selections (only returning the first selection). So changed it to `Union(Selection.Rows, Selection).Count` instead which works much works better! It does count the same row multiple times though. Something new for me to think about. – Christofer Weber Dec 03 '18 at 16:02
  • 1
    `Selection.Rows.Count` doesn't work because it only shows the row count of the first area that is selected. You must loop through the `Selection.Areas` and add all counts together. See my edit. – Pᴇʜ Dec 03 '18 at 16:10
  • That's great. That solves the last two problems at once. I'll edit what I did into the question. – Christofer Weber Dec 03 '18 at 16:25
0

Maybe a bit on the side, but I came over this post looking for error on delete selection on a filtered table. So maybe it may help someone else. If you are getting the error message "Deletion method of Range class failed". Run-time error '1004'.

I have a table in my worksheet named "Table_1". Selecting the table or a column in the table and then doing the .EntireRow.Delete syntax throws me an error. Selecting the full table, excluding header, and then just doing .Delete works fine. Thus leaving the simple code:

    Application.DisplayAlerts = False   'To avoid the pop-up when delete the rows.
    Range("Table_1").SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True

Any filtering ect. you'll have to do in your normal way.

I hope this may help someone.

Thanks BR Fnk

Fnk
  • 50
  • 7