Okay, so I want to start off by saying I am self taught and very new to coding. I am trying to write a macro that can find all values greater than 6 in the Rep column, delete the entire row, and insert a blank row. I have tried For Each Next loop, With and Do While. The data set has over 5000 rows so I chose the column as range but it won't go to the next or the app crashes. I have been searching the internet for answers but there are very few useful sources for what I'm trying to do. The code I have now is kind of a mash of a lot of different approaches. Hopefully one of you guys can guide me in the right direction to get this macro functional. This is what I've got:

    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!")

CodePudding user response：

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

CodePudding user response：

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'