Delete all rows that match the cell value simultaneously with command button

aroig07 · Feb 22nd 2021

Hello there !!!

Sorry I am posting all my questions here, just that I find that in this forum people are more responsive I have this code that works, but not the way I want it to. I want the loop to delete all the rows that match the cell value with the click of the button. At the moment I have to click through and they delete one by one with each click. Is there a way that it can just go over the work Sheet and delete at the same time ???

Thank youuu in advance !!!

Code

Sub DeleteMyRows()

Dim ws As Worksheet
Dim LastRow As Integer
Dim student As String
Dim i As Integer

Set ws = Worksheets("DataBase")
LastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row

student = Worksheets("Summary").Cells(7, "H")

i = 1

    Do While ws.Cells(i, 3) <> ""
      If ws.Cells(i, 3).Value = student Then
        ws.Cells(i, 3).EntireRow.Delete
      End If
    
    i = i + 1
    
    Loop
      
End Sub

Display More

dangelor · Feb 23rd 2021

Possibly...

Code

Sub DeleteMyRows2()
    Dim ws As Worksheet
    Set ws = Worksheets("DataBase")
    
    Dim student As String
    student = Worksheets("Summary").Cells(7, 8)
    
    With ws.Cells(1, 3).CurrentRegion
        .AutoFilter 3, student
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
End Sub

Display More

jolivanes · Feb 23rd 2021

Re: "Sorry I am posting all my questions here...."

Why? I am sure everyone here is glad to see you here. Don't be shy. Come back anytime.

The code from dangelor has you covered and is faster also.

In your code, if you change this

Code

i = 1
Do While ws.Cells(i, 3) <> ""

to this

Code

For i = LastRow To 1 Step - 1    '<----- When deleting, always start from the end.

and

Code

i = i + 1
Loop

to this

Code

Next i

You would have had a home run.

You could also have the three lines from If to End If in a single line like so

Code

If ws.Cells(i, 3).Value = student Then  ws.Cells(i, 3).EntireRow.Delete

But stick to dangelor's code as that is the way to go.

Delete all rows that match the cell value simultaneously with command button

Participate now!

Share

Tags