How to Delete Multiple cells using ListBox Multi selection

sai85

New Member
Joined
Apr 29, 2013
Messages
49
If I have selected more than one selection in List Box, I am not able to delete all of my selected values. It either Deleting from Top or Bottom. I have already searched for codes in internet and whatever i got, it is not working for me.Code given for your reference:

Private Sub Cmd_Del_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet1")
Dim i As Long
For i = Me.LB_ZoneRegion.ListCount - 1 To 0 Step -1
If Me.LB_ZoneRegion.Selected(i) = True Then
sh.Range("A" & i + 2 & ":B" & i + 2).Select
Selection.Delete
End If
Next i
Call UserForm_Initialize
End Sub


Private Sub UserForm_Initialize()
On Error Resume Next
With Me.LB_ZoneRegion
.Clear
.ColumnCount = 2
.ColumnHeads = True
.ColumnWidths = "40;50"
.RowSource = "ZoneRegion"
.MultiSelect = fmMultiSelectMulti
End With
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,297
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't that code work?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
Private [COLOR="Navy"]Sub[/COLOR] Cmd_Del_Click()
[COLOR="Navy"]Dim[/COLOR] sh [COLOR="Navy"]As[/COLOR] Worksheet, Rng [COLOR="Navy"]As[/COLOR] Range
 [COLOR="Navy"]Set[/COLOR] sh = ThisWorkbook.Worksheets("Sheet1")
 [COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 [COLOR="Navy"]For[/COLOR] i = 0 To Me.LB_ZoneRegion.ListCount - 1
    [COLOR="Navy"]If[/COLOR] Me.LB_ZoneRegion.Selected(i) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Rng = Range("A" & i + 1).Resize(, 2)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Rng = Union(Rng, Range("A" & i + 1).Resize(, 2))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]If[/COLOR] Not Rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] Rng.EntireRow.Delete
Call UserForm_Initialize
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top