Delete cells in a worksheet table based matching text in a combobox in userform

Brenigt · Feb 16th 2017

Hi,
I'm looking for VBA code that will allow me to delete cells in a worksheet based on text that has been picked in a dropdown list in a combobox in a userform when I click a button. Can you one help???

I have found this code but it doesn't seem to work?:

Private Sub cmdDelete_Click()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim strSearch As String
Dim aCell As Range
On Error GoTo Err
Set ws = Sheets("Data")
lastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
strSearch = ComboBox1.Value
Set aCell = ws.Range("D" & lastRow).Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
ws.Rows(lastRow).Delete
End If
Exit Sub
Err:
MsgBox Err.Description
End Sub

Logit · Feb 16th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Are the cells you want to clear in Column D ? Or are they in another column or scattered around the worksheet ?

Is your worksheet name "Data" ?

And you want to clear the cell only, not the entire row where the cell is located ... correct ?

Brenigt · Feb 17th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi yes that's correct. I do have a few other user forms that I need to Delete a record from a table that's 4 columns wide, but thought it easier to start with this as it's only 1 column wide.

Thanks for replying

Logit · Feb 17th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Search / Delete macro is case sensitive. Will delete Paris but not paris.

Code

Option Explicit




Sub cmdDelete_Click()
Dim Cel As Range, Range As Range
Dim str As String
    Set Range = Sheets("Sheet1").Range("D1:D6500") 'edit as required
    str = ComboBox1.Value
        
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & str & "*" Then
            Cel = Replace(Cel, str, "")
        End If
    Next Cel
    Application.ScreenUpdating = True




End Sub

Display More

Brenigt · Feb 17th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi Logit,

Thanks for the help. I'm an absolute virgin when it comes to VBA, so please be patient.

I would like to delete the cells totally so that the dynamic list the data is held in and appears in the comobox has not got any blank cells. Is this simple to do?

Logit · Feb 17th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Code

Sub cmdDelete_Click()
Dim Cel As Range, Range As Range
Dim str As String
    Set Range = Sheets("Sheet1").Range("D1:D6500") 'edit as required
    str = ComboBox1.Value
        
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & str & "*" Then
            Cel.Delete  '= Replace(Cel, str, ")     '<--- comment out this part and insert Delete
        End If
    Next Cel
    Application.ScreenUpdating = True




End Sub

Display More

Don't worry about the questions. The only way to learn is to ask. I'm the dimmest bulb in the box. 8-)

Brenigt · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi Logit,

Thanks for the code. I have tried it and it works but seems to take a long time for it to run through the code when I click the delet button. Is there any way of speeding up the code?

Cheers
Brenigt

Logit · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

How many rows or cells are you looking at ?

Logit · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

I just tested the macro project here. Used the word PARIS that was entered into 65000 rows. Took less than one second to clear all those entries.

Brenigt · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi Logit,

If it helps answer my question I have attached the file I have created. The only thing now not working is the delete buttons in the 'manage list userforms'

I have inserted your code but it runs very slow and crashes the file occasionally.

Any advice will be gratefully recieved.

Cheers

Brenigt

Logit · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Excel from at least 2007 and newer utilizes a max of 1,048,576 rows.

Setting the range to ("D:D") is instructing the macro to review each cell, one at a time from the top to the bottom of the column.

If you change the range to :

Code

Private Sub cmdDelete_Click()




    Dim Cel As Range, Range As Range
    Dim str As String
    Set Range = Sheets("Data").Range("D1:D50")  '<----    This smaller range
    str = ComboBox1.Value
     
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & str & "*" Then
            Cel.Delete
        End If
    Next Cel
    Application.ScreenUpdating = True
     
'Clear Text Box'
'
    Textbox1.Value = ""




'Message when data has been sent'
'
    MsgBox ("Builder/Developer DropDown List Updated")
    
End Sub

Display More

It gives the macro an opportunity to review a smaller range, making it lots faster.

Brenigt · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Each drop down list is very small in size. Some only 1 or 2 rows big with some 10-15 rows. Also one table is 1 column wide, 1 is 2 columns wide, the rest 4 columns wide. I have posted the file for you to have a look. Hopefully you can see what I'm trying to achieve!

Logit · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Excel ver 2007 and newer (I believe) utilizes over 1 million rows possible.

The way the macro is presently set, it is reviewing each cell, individually row by row for all 1 million +.

Changing the macro to a smaller range will speed things up for you :

Code

Private Sub cmdDelete_Click()




    Dim Cel As Range, Range As Range
    Dim str As String
    Set Range = Sheets("Data").Range("D2:D50")    '<-- smaller range here. only 50 rows
    str = ComboBox1.Value
     
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & str & "*" Then
            Cel.Delete
        End If
    Next Cel
    Application.ScreenUpdating = True
     
'Clear Text Box'
'
    Textbox1.Value = ""




'Message when data has been sent'
'
    MsgBox ("Builder/Developer DropDown List Updated")
    
End Sub

Display More

Brenigt · Feb 20th 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi Logic,
Just seen you recent post. Thanks for that and makes sense. Is it possible to count the amount of active cells rather than limiting the amount of cells in he range to be reviewed. I only ask because the lists are dynamic and although I doubt they will get to 50 rows high, who knows in the future and I don't want to be altering the coded I can automate it.
cheers

Logit · Feb 21st 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Here is an update to the macro code for the first file you posted :

Code

Option Explicit








Sub cmdDelete_Click()
 Dim Cel As Range, Range As Range
    Dim str As String
    With Sheets("Sheet1")
    Set Range = .Range("D2:D" & .Cells(.Rows.CountLarge, 4).End(xlUp).Row)
    End With
    str = ComboBox1.Value
     
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & str & "*" Then
            Cel.ClearContents
        End If
    Next Cel
    Application.ScreenUpdating = True




    
End Sub

Display More

Takes about 1 second to clear 70,000 cells

Brenigt · Feb 22nd 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi Logitech,

Thanks for the revised code. It's working but only clearing 1 cell in 1 column. Can this code be easily changed to allow clear data in 1 row over 2 columns. I need to be able to delete a name and telephone number in a table based on the name pick in the combobox drop down. I have tried to make it work but failed dismally. What would be a big help would be if you could comment on what each line is doing so that I can learn from it.

cheers
Brenig

Logit · Feb 22nd 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Your were still using the old macro for delete. Here is the new macro that runs alot faster. It has already been added to the project attached.

Code

Private Sub cmdDelete_Click()
 Dim Cel As Range, Range As Range
    Dim str As String
    With Sheets("Data")
    Set Range = .Range("D2:D" & .Cells(.Rows.CountLarge, 4).End(xlUp).Row)
    End With
    str = ComboBox1.Value
     
    Application.ScreenUpdating = False
    For Each Cel In Range
        If Cel Like "*" & str & "*" Then
            Cel.ClearContents
        End If
    Next Cel
    Application.ScreenUpdating = True
     
Dim rng As Range




'Store blank cells inside a variable
  With Sheets("Data")
    Set rng = .Range("D2:D" & .Cells(.Rows.CountLarge, 4).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
  
'Delete blank cells and shift upward
  rng.Rows.Delete Shift:=xlShiftUp
  End With
'Clear Text Box'
'
    Textbox1.Value = ""




'Message when data has been sent'
'
    MsgBox ("Builder/Developer DropDown List Updated")
    
End Sub

Display More

I also added the new delete macro to the following forms:

frmBIImList
frmBuildList
frmCSList
frmTecList
frmUpdateLists

If it does not belong in any of those form's coding, please remove as appropriate.

Brenigt · Feb 22nd 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi logit,

Huge thanks for the new code. You are a genius!!

I have run the file but keep getting an error on the following code line. It keeps telling me that no empty cells were found. I have no idea what is wrong with it. Can you help again?
Cheers Brenig

Set rng = .Range("D2:D" & .Cells(.Rows.CountLarge, 4).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)

Brenigt · Feb 22nd 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Hi logit,

Please ignore my last reply. The problem was the cell reference in ("D2:D" & .Cells(.Rows.CountLarge, 4). Once I changed the cell reference and adjusted column reference in the code it worked. The only problem now is if a name is picked in the middle of the list the following code does not work. ' rng.Rows.Delete Shift:=xlShiftUp '
Can help again??

Cheers
Brenig
rng.Rows.Delete Shift:=xlShiftUp

Logit · Feb 22nd 2017

Re: Delete cells in a worksheet table based matching text in a combobox in userform

Yup ... I was working on that and then you corrected it. Great !

I don't understand why you're experiencing the last issue. Shouldn't make any difference where the row is to be deleted.

Does it do that for you on all delete macros or just one in particular?

ps: I'm not a genius .. just slept at a Holiday Inn Express last night.

Delete cells in a worksheet table based matching text in a combobox in userform

Participate now!

Share