How do I use Find and Loop in VBA for Excel to identify, delete, and insert blank row for values greater than 6?

user_18335615 0
Viewed 83

I am trying to find all values greater than 6 in the Rep column, delete the entire row, and insert a blank row.

I tried For Each Next loop, With and Do While. The dataset has over 5000 rows so I chose the column as range but it won't go to the next or the app crashes.

I searched the internet but there are few useful sources for what I'm trying to do. The code I have is a mash of approaches.

Public Sub DRS_FindAll_Delete()

Dim c As Range
Dim firstAddress As String
Dim WorkRng As Range

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range (Column)", xTitleID, WorkRng.Address, Type:=8)

Dim x As Integer

x = xlValues > 6

For Each c In WorkRng
    Set c = Cells.Find(x, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            x.EntireRow.Delete
            Set c = Cells.FindNext(c)
        Loop While Not c Is Nothing
    End If
Next
MsgBox ("All done!")
vba excel
2 Answers

Clear Entire Rows

A Few Issues

  • If you cancel the input box, an error will occur.
  • What does the line x = xlValues > 6 do? If we know that xlValues = -4163 then x will be equal to a False converted to an integer i.e. x = 0. To conclude, your procedure will clear all (entire) rows whose cells in the selected column are equal to 0, if you replace x.EntireRow.Delete with c.EntireRow.Clear.
  • Once a cell (c) has been found and cleared, firstAddress = c.Address becomes redundant. You're not using it anyway.

A Different Approach

  • Whatever is selected via the input box, only the first cell is considered. It will assume that the column of the first cell contains one row of headers (row 1) and will use the cells up to the last non-empty cell. By using AutoFilter, it will filter all values greater than 6 and finally, clear the entire rows of the filtered cells.
Option Explicit

Sub DRS_FindAll_Clear()
    
    Const Criteria As String = ">6"
    Const aibPrompt As String = "Select a cell in the desired column"
    Const aibTitle As String = "DRS_FindAll_Clear"
    Dim aibDefault As String
    If TypeOf Selection Is Range Then
        aibDefault = Selection.Address
    End If
    
    Dim WorkRng As Range
    On Error Resume Next
        Set WorkRng = Application.InputBox( _
            aibPrompt, aibTitle, aibDefault, , , , , 8)
    On Error GoTo 0
    If WorkRng Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet: Set ws = WorkRng.Worksheet
    If ws.FilterMode Then ws.ShowAllData
    
    Dim strg As Range ' Table Range (has headers)
    With ws.Columns(WorkRng.Column)
        Dim lCell As Range: Set lCell = .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column
        Set strg = .Cells(1).Resize(lCell.Row)
    End With
    
    Dim sdrg As Range ' Data Range (no headers)
    Set sdrg = strg.Resize(strg.Rows.Count - 1).Offset(1)
    
    strg.AutoFilter 1, Criteria
    
    Dim svdrg As Range ' Data Visible Range (no headers)
    On Error Resume Next
        Set svdrg = sdrg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    ws.AutoFilterMode = False
    If svdrg Is Nothing Then Exit Sub
    
    svdrg.EntireRow.Clear
    
    Application.ScreenUpdating = True
    
    MsgBox "All done!"

End Sub
user_98140690

This is the second time today this issue has come up in a SO question.

For each c in Workrng

is incorrect when you are deleting items from a collection (and yes workrng is a collection).

When you delete items from a collection in a loop you must not change the part of the collection that still has to be iterated over.

Lets say you have rows 1 to 10 and you have reached row 3 which you now delete. When you do this, there will only be 9 rows. However, the control variable for the for each doesn't know you have deleted a row so its still counting to 10, even worse its going to skip a row, because what was row 4 is, after your deletion, now row 3. So when the control variable looks for row 4 it will actually be getting what was row 5, so the old row 4( which is now row 3) doesn't get processed at all.

Thus for collections you can only safely delete the last item in the collection. Consequently you can't use 'for each' you must use 'for i = count to 1 step -1'

user_71773460
Related