Making a Row Delete Macro run better

[Jnrrpg11]

Hello Everyone,

I should start by saying I've only a very basic grasp of VBA.

Some of the guys on this forum have helped me with some VBA code to delete unwanted rows of data from a spreadsheet, which does exactly what it should so thanks for that.

Only thing is that i've run it with the same source data over and over in testing it may take 3 minutes to process or 10 minutes. I can't understand what would cause it to be so drastically different.

The code is here -

Sub MainRoutineA()


Dim ws As Worksheet
Dim Rng As Range
Dim lastRow As Long


Application.ScreenUpdating = False


Set ws = ActiveWorkbook.Sheets("FY Budget from ART")


lastRow = ws.Range("I" & ws.rows.Count).End(xlUp).Row




Set Rng = ws.Range("I5:I" & lastRow)


With Rng
.AutoFilter Field:=1, Criteria1:="<>" & Worksheets("User Information").Range("I34").Value
.SpecialCells(xlCellTypeVisible).EntireRow.Delete

EndWith


ws.AutoFilterMode = False


Application.ScreenUpdating = True




EndSub



In the workbook, a user will select their name from a dropdown on "User Information" - "I34".
The Code will look down column I and remove any instances where that users name doesn't exist.

Is there any tips for using this type of code that would potentially speed it up or can you see any red flags that cause known speed issues in the above code?
If it helps the source data will average about 11,000 rows and once run cut the data to maybe 250-750 Rows.


As always in advance.

Richard

 

[Fluff]

On 12000 rows it only takes 24 secs
Do you have any event code in your sheet?
Also do you have lots of formulae?

 

[Jnrrpg11]

Hey Fluff,

Thanks for replying.
The answer to your question is yes, there's lots of formula in the workbook. Is there a way of halting all calculations until the macro runs. I've used DoEvents before but I'm not sure if it's right in this case or where to put it or if there's a better option. There's also a lot of event code but not relating to this sheet.

Thanks again.

 

[Fluff]

You've already turned screenupdating of/on so add the calculation

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

If its still taking a long time, let me know.

 

[SpillerBD]

Application.Calculation = xlManual

& then back to automatic....

Application.Calculation = xlAutomatic

 

[Jnrrpg11]

So the application calculation is always off. I use code 'this workbook' to ensure that.

Sub Workbook_Open()


Application.Calculation = xlManual
Application.CalculateBeforeSave = False




End Sub




Sub Workbook_BeforeClose(Cancel As Boolean)


Application.Calculation = xlAutomatic
Application.CalculateBeforeSave = True




End Sub

Once the user pastes their information in and clicks calculate the sheet runs the macros to thin out the source data.
Once this takes place it calls a macro that manually calculates the sheets (which look more like dashboards).

Interestingly, i remember using this macro before and it ran really quickly so I'm not sure why it so slow now.

 

[tonyyy]

If you have Conditional Formatting applied, you might consider deleting it to see if it's impacting performance.

Based on one project experience, CF can also be a memory hog, increasing in size as more iterations of the macro are run, and slowing execution with each iteration.

Cheers,

tonyyy

 

[Peter_SSs]

.. it may take 3 minutes to process or 10 minutes. I can't understand what would cause it to be so drastically different.

..can you see any red flags that cause known speed issues in the above code?

Apart from things already mentioned in the thread, what can cause significant differences in run-time for such a macro is the number of disjoint ranges that need to be deleted. For example, consider the sheet below where the aim is to delete the green rows from each table. Both tables have 6 green rows but Table 1 only has 2 ranges to delete while Table 2 has 6 ranges to delete. The deletion for Table 2 will be slower than for Table 1.

Book1
	A
1	Table 1
2	a
3	b
4	c
5	d
6	e
7	f
8	g
9	h
10	i
11	j
12	k
13
14
15	Table 2
16	a
17	b
18	c
19	d
20	e
21	f
22	g
23	h
24	i
25	j
26	k
Sheet2

Is there any tips for using this type of code that would potentially speed it up ..

Yes - use the above information to gather all the deletion rows together so that there is only one section of rows to delete.

I haven't set the Calculation mode in my code as you have said that has already been taken care of. Give this a try in a copy of your workbook but also check my note below the code.

Sub Del_Rows()
  Dim a As Variant, b As Variant
  Dim CompVal As String
  Dim nc As Long, i As Long, k As Long, lastrow As Long
  
  CompVal = ActiveWorkbook.Worksheets("User Information").Range("I34").Value
  With ActiveWorkbook.Sheets("FY Budget from ART")
    nc = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
    lastrow = .Range("I" & .Rows.Count).End(xlUp).Row
    With .Rows("6:" & lastrow)
      a = .Columns(9).Value
      ReDim b(1 To UBound(a), 1 To 1)
      For i = 1 To UBound(a)
        If a(i, 1) <> CompVal Then
          b(i, 1) = 1
          k = k + 1
        End If
      Next i
      If k > 0 Then
        Application.ScreenUpdating = False
          .Columns(nc).Value = b
          .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
          .Resize(k).EntireRow.Delete
        Application.ScreenUpdating = True
      End If
    End With
  End With
End Sub

Note:
The code is case-sensitive so if 'User Information' cell I34 could be "Fred" but column I of the deletion sheet could contain "fred", "FRED" etc then change these two lines in the code.

CompVal = LCase(ActiveWorkbook.Worksheets("User Information").Range("I34").Value)

If LCase(a(i, 1)) <> CompVal Then