Code running slow, is there a better method?

[dreid1011]

I just wrote up a small bit of code to delete rows based on results of a CountIf and CountA. I tried looking up alternatives to using these with WorksheetFunction once I realized the code was taking a long time to run, but I came up empty.

So, if I find the need to employ this code again in the future, I would like to at least know what can be changed to make it run faster.

I ran it on about 8000 rows, and it took 10-15 minutes to delete ~3000 rows.

The code is looking for duplicate rows based on account number in col d, and deleting the row if it is a duplicate AND columns S, T, U are empty.

Sub CDelRows()
Dim lRow As Long, i As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
'MsgBox lRow
Application.ScreenUpdating = False
For i = lRow To 2 Step -1
If WorksheetFunction.CountIf(Range("D2:D" & i), Range("D" & i).Value) > 1 Then
    If WorksheetFunction.CountA(Range("S" & i & ":U" & i)) = 0 Then
        Range("A" & i).EntireRow.Delete
    End If
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[Norie]

There's nothing much you can do to speed up that specific code other than the usual things of turning of automatic calculation and screen updating.

In general though you might want to consider using a different approach, e.g. using a filter to return the rows you want to delete.

 

[dreid1011]

I'm not familiar with using filters in VBA, but I can give it a go. I was just curious if there was something else I could have used other than WorksheetFunction.CountIf and CountA. I thought those might have been the reason it was so slow. I only say that because I have used other delete row codes on more data that run much quicker than this does. Though, those are only checking a single cell per row for a specified value.

Thank you for the suggestion.

 

[offthelip]

There is a much faster way of doing it: use your if condition to write "TRUE" or "False" in a spare column to the right of your data, then sort the data on this column, then delete the entire range of "trues".
An alternative way which only works if there are no formula in the worksheet, which is to load the entire sheet into a variant array, clear the entire sheet. load the cleared sheet into a second array, then run down the first array and copy each row you want to keep across to the output array. Then write the output array back to the worksheet. this would take only a few seconds to complete

 

[dreid1011]

Thank you, I'll take a look into that as well, once I figure out how to manipulate multi-dimensional arrays.

 

[Norie]

When you load data into an array from a range the array will have only 2 dimensions, basically rows and columns.

 

[dreid1011]

When you load data into an array from a range the array will have only 2 dimensions, basically rows and columns.

Right, I understand that part. It's just working the logic out in my head. I haven't used arrays in coding much at all, though I understand how they work.. it's another thing writing out the proper code for it. And sometimes I'm not the most elegant or efficient coder. Anyways, I just need to play around with some and get a feeling for it.

 

[pvr928]

Hi dreid1011

Using an array to determine which rows to delete will certainly speed up that part of the code (philosophy: read from the worksheet once, perform calculations in memory (via an array in this case), rather than: read from worksheet, perform calculation, read from worksheet, ....).

However, I would also look into the makeup of the worksheet from which you want to delete rows. It could be that it is the act of deletion that is causing the slow running. Do you have a lot of complicated formulae in that worksheet? Are they referring to lots of rows (ie are you using notations such as 'A:A' when referring to Column A (ie the entire column))? Deleting rows (or conversely inserting rows) may be a very slow process in this case.

If not, I second Norie's and offthelip's suggestions.

Cheers

pvr928

 

[offthelip]

I managed to find the time to write the subroutine to do this using arrays. I assumed 100 rows and 26 columns because I didn't know the size of your worksheet. obviously change these or detect them automatically using End(xlUp).Row

Sub test()
inarr = Range(Cells(1, 1), Cells(100, 26))
Range(Cells(1, 1), Cells(100, 26)) = ""
outarr = Range(Cells(1, 1), Cells(100, 26))
For i = 100 To 2 Step -1
  
  For j = i - 1 To 1 Step -1
   If inarr(i, 4) = inarr(j, 4) Then
    ' row is is duplicated in col d
     If (inarr(i, 19) = "" And inarr(i, 20) = "" And inarr(i, 21) = "") Then
      ' delete this row
       inarr(i, 26) = "TRUE"
       
     End If
     Exit For
   End If
  Next j
Next i
indi = 1
For i = 1 To 100
 If inarr(i, 26) <> "TRUE" Then
  For k = 1 To 26
  outarr(indi, k) = inarr(i, k)
  Next k
  indi = indi + 1
 End If
Next i


Range(Cells(1, 1), Cells(100, 26)) = outarr
     
     
End Sub

 

[dreid1011]

Hi dreid1011

Using an array to determine which rows to delete will certainly speed up that part of the code (philosophy: read from the worksheet once, perform calculations in memory (via an array in this case), rather than: read from worksheet, perform calculation, read from worksheet, ....).

However, I would also look into the makeup of the worksheet from which you want to delete rows. It could be that it is the act of deletion that is causing the slow running. Do you have a lot of complicated formulae in that worksheet? Are they referring to lots of rows (ie are you using notations such as 'A:A' when referring to Column A (ie the entire column))? Deleting rows (or conversely inserting rows) may be a very slow process in this case.

If not, I second Norie's and offthelip's suggestions.

Cheers

pvr928

The sheet is very simple. Data export from an Access database, sorted ascending by account number with no formulas on this particular sheet. There is a single conditional formatting rule for the entire data set though.

I managed to find the time to write the subroutine to do this using arrays. I assumed 100 rows and 26 columns because I didn't know the size of your worksheet. obviously change these or detect them automatically using End(xlUp).Row

Sub test()
inarr = Range(Cells(1, 1), Cells(100, 26))
Range(Cells(1, 1), Cells(100, 26)) = ""
outarr = Range(Cells(1, 1), Cells(100, 26))
For i = 100 To 2 Step -1
  
  For j = i - 1 To 1 Step -1
   If inarr(i, 4) = inarr(j, 4) Then
    ' row is is duplicated in col d
     If (inarr(i, 19) = "" And inarr(i, 20) = "" And inarr(i, 21) = "") Then
      ' delete this row
       inarr(i, 26) = "TRUE"
       
     End If
     Exit For
   End If
  Next j
Next i
indi = 1
For i = 1 To 100
 If inarr(i, 26) <> "TRUE" Then
  For k = 1 To 26
  outarr(indi, k) = inarr(i, k)
  Next k
  indi = indi + 1
 End If
Next i


Range(Cells(1, 1), Cells(100, 26)) = outarr
     
     
End Sub

Thank you for that. I will keep this in mind as I play with arrays more.

Thanks for all the suggestions, my need for the code is done for now. But there will be more use for something better in the future.