Delete Multi selected rows

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi i have a code below and i want manually selected rows all at once to delete however if i have the 1st msgbox then it just says no rows selected


VBA Code:
Sub deleterows()
Dim i As Long, irow As Long
Dim oSel As Range
Dim answer As String
Set oSel = Selection

If oSel.Areas.Count <= 0 Then 'if i delete this code of line then it just gives the msgbox to delete regrdless of not selected of any rows
MsgBox "rows not selected"


ElseIf oSel.Areas.Count <= 1 Then
answer = MsgBox("Would u like to delete?", vbQuestion + vbYesNo, "Confirm to delete")
                       If answer = vbYes Then
                       
 For i = oSel.Areas.Count To 1 Step -1
 oSel.Areas(i).EntireRow.Delete xlUp
 Next i
    End If
End If

End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Hello Hsolaki,
try to change operator to "greater then" and
add "On error resume next" in a case that same row is selected multiple times...
VBA Code:
Sub deleterows()

    Dim i As Long, irow As Long
    Dim oSel As Range
    Dim answer As String
    Set oSel = Selection
    
    If oSel.Areas.Count <= 0 Then 'if i delete this code of line then it just gives the msgbox to delete regrdless of not selected of any rows
        MsgBox "rows not selected"
    ElseIf oSel.Areas.Count >= 1 Then
        answer = MsgBox("Would u like to delete?", vbQuestion + vbYesNo, "Confirm to delete")
        If answer = vbYes Then
            For i = oSel.Areas.Count To 1 Step -1
                On Error Resume Next
                oSel.Areas(i).EntireRow.Delete xlUp
            Next i
        End If
    End If

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,749
Office Version
  1. 365
Platform
  1. Windows
This line If oSel.Areas.Count <= 0 Then will never result in true, as the selected area/cells is always at least 1.
Are you selecting entire rows, or just individual cells?
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Yes, if you also want to display message turn this to
VBA Code:
If oSel.Areas.Count = 1
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff / Excel max i am selecting entire rows. @EXCEL MAX code does not work if 1 entire row is selected however if i select 2 rows then it works. is there any way around if one cell is selected then msgbox comes up with "No rows selected" and if one entire row is selected then msgbxo to delete or not delete?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,749
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub hsolanki()
   If Selection.Count Mod Columns.Count <> 0 Then
      MsgBox "Please select entire rows only"
   ElseIf MsgBox("Are you sure you want to delete these rows?", vbYesNo) = vbYes Then
         Selection.Delete
   End If
End Sub
 
Solution

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff you are an amazing 😊. it worked as i wanted to.

Thank you once again for all your kindness and all your help once again 😌
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,749
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,068
Messages
5,628,458
Members
416,318
Latest member
Mano130898

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