1

I am relatively new to VBA. I have made a tool for cleaning my cross references.

The way it works, it that i have the complete raw list which i loop through to delete rows where a certain condition is met.

Example:

'RACKS LOOP
'if checkbox is checked
If Sheet1.CheckBox14.Value = True Then

'loop
 Dim rngRK As Range
 Dim iRK As Integer, counterRK As Integer

 'Set the range to evaluate to rngRK.
 Set rngRK = Range("D1:D32000")

 'initialize iRK to 1
 iRK = 1

 For counterRK = 1 To rngRK.Rows.Count

      'If cell i in the range contains the value: "RACKS",
       'delete the row.
        If rngRK.Cells(iRK) = "RACKS" Then
            rngRK.Cells(iRK).EntireRow.Delete
        Else
        'Else increment i
            iRK = iRK + 1
        End If

 Next
'end loop
Else
End If

I have 30 loops like the above in this file, as i have many different conditions i need to be able to choose from. The only issue is that i think it takes a lot of time to process.

Is there any smarter/faster way to do this?

Tue Larsson
  • 21
  • 1
  • 3
    First of all, if you are deleting rows, you have to step backwards or you risk skipping rows. Secondly, you can build a new range of rows as you go in your loop, and then delete that range after the loop is done. The multiple deletes are the slow part. – braX Jan 13 '20 at 10:56
  • 1
    As well you may try to use `Application.ScreenUpdating = False` at the beginning of the sub and `Application.ScreenUpdating = True` at the end. – Vitaliy Prushak Jan 13 '20 at 10:59
  • 1
    third, counterRK must be a `Long`. But the performance issue is due to multiple deletes. You should rather sort your data on col D, then find first and last row to delete, and delete all the bunch at once. – iDevlop Jan 13 '20 at 11:00
  • 1
    Filtering is probably the fastest way to go about this. Select all the rows with your criteria, then delete the visible rows.Looping is very inefficient for this kind of task – Nick Jan 13 '20 at 11:11
  • Thank you all for the input. I think the filter solution combined with turning off screen update. may be a very viable option (especially because of the way the data is structured). Thank you! – Tue Larsson Jan 14 '20 at 15:20

0 Answers0