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

Joined
Feb 28, 2022
Messages
1
Reaction score
0
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 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Staff online

Members online

Forum statistics

Threads
473,767
Messages
2,569,571
Members
45,045
Latest member
DRCM

Latest Threads

Top