Help with VBA find loop?

[simc22]

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

I also tried to update it after some input but now my problem is that it is not selecting the cells/ basically not performing any actions. Here's the updated code (the preceding declarations are the same) :

For i = Count To 1 Step -1
Set c = Cells.Find(x, LookIn:=xlValues)
If c.Value > 6 Then
Do
c.EntireRow.Clear
Loop While c Is Nothing
End If
Next
MsgBox ("All done!")

End Sub

 

[Andrzejek]

> find all values greater than 6 in the Rep column, delete the entire row, and insert a blank row.

Let's say the Rep column is column B and you have values in this column (not blanks) and your data starts in Row 2 with header row in row 1
And instead of Deleting a row and Inserting a row, let's just Clear it

Option Explicit

Sub Test()
Dim i As Integer

i = 2
Do While Cells(i, 2).Value <> ""
    If Cells(i, 2).Value > 6 Then
        Rows(i & ":" & i).ClearContents
    End If
    i = i + 1
Loop

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

simc22,
Please take note of Andy's reply and his use of TGML code tags. Notice how much easier it is to read because of the formatting and indents. Just select all your code and click the Code icon above the reply/post box.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

Hi Key-Stoner and welcome to Tek-Tips.

When using a loop to delete rows in a table you need to start with the bottom row of the table and work up.

But it's much simpler to use a Filter to identify rows you need to delete or clear the contents of. Basically a 2-step process that doesn't require a macro.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

I am self taught and very new to coding

Executing code line by line and observing values in 'Locals' window is one of methods in debugging the code.

Samples your code:
[tt]Dim x As Integer
x = xlValues > 6[/tt]
as xlValues=-4163, VBA first evaluates xlValues > 6 as False, next converts it to Integer, finally assigns x=0. Probably it is not your intention.

[tt]Dim x As Integer
...
x.EntireRow.Delete ' x is not a range object[/tt]
Also, note that Clear and Delete treat rows differently.

[tt]In:
For Each c In For Each c In WorkRng
Set c = Cells.Find(x, LookIn:=xlValues)[/tt]
the first line loops through WorkRng and assigns cell by cell to c. In next line you assign to c cells with 0s. Moreover, as Cells without top range refers to active worksheet, you search in the whole sheet. I guess that you planned [tt]Set OtherC = WorkRng.Find(x, LookIn:=xlValues)[/tt].
Etc.



combo

 

[strongm]

Of course, you can do it without looping at all (which is what Skip is alluding to, although he is talking about a manual process, but can of course do that in code)...