Excel macro - delete rows

[bart1975]

I need an excel macro to delete certain rows in a spreadsheet based on one column of the spreadsheet. If there is a "comma" in the name field i want the row deleted. I want a pause built in so I can refuse the delete before it happens. Any help on this would be much appreciated.

 

[Boller]

Do you want a "pause" before the deletion of each row or a one-time "pause" before all deletions are done ?

 

[crimson_b1ade]

items in 'red' below you will have to change depending on what column your field name resides in (in the below example it is in Column A):

Sub DeleteRows()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim i As Long
Dim s As Integer
For i = LastRow To 2 Step -1
    s = InStr(1, Range("A" & i), ",")
    
    If s > 0 Then
        
        Rows(i).Interior.ColorIndex = 36
        
        r = MsgBox("Delete highlighted row?", vbYesNo, "Delete Rows Prompt")
            
        If r = 6 Then
               Rows(i).Delete
            Else
               Rows(i).Interior.ColorIndex = 0
        End If
                
    End If
Next i
End Sub

 

[bart1975]

Thank you, that macro works very well, the only adjustment to it would be to page up as you delete rows. The page stays where you deleted the first row so you cant see the rows being deleted after the first page. I hope I'm making sense.

 

[crimson_b1ade]

see below modification

Sub DeleteRows()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim i As Long
Dim s As Integer
For i = LastRow To 2 Step -1
    s = InStr(1, Range("A" & i), ",")
    
    If s > 0 Then
        
        With Rows(i)
            .Interior.ColorIndex = 36
            .Activate
        End With
        
        r = MsgBox("Delete highlighted row?", vbYesNo, "Delete Rows Prompt")
            
        If r = 6 Then
               Rows(i).Delete
            Else
               Rows(i).Interior.ColorIndex = 0
        End If
                
    End If
Next i
End Sub

 

[bart1975]

Thank you, that works great. One of these days I'm going to learn how to write some code. You're good and very fast!