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

Feb 28, 2022
Reaction score
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
Set c = Cells.FindNext(c)
Loop While Not c Is Nothing
End If
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
Loop While c Is Nothing
End If
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

Latest member

Latest Threads