Change/Amend VBA Code To Run Fast

[hrayani]

Hello Friends,

I have a set of data which contains shipment records. I have to hide the rows where Column O
shows the status of the Shipment.

I am using the below mentioned code to hide rows based on Column O

Private Sub CommandButton1_Click()

For a = 3 To 500

If Worksheets("RO").Cells(a, 15).Value = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = False
End If
If Worksheets("RO").Cells(a, 15).Value = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = True
End If
Next

End Sub

Private Sub CommandButton2_Click()

For a = 3 To 500

If Worksheets("RO").Cells(a, 15).Value = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = True
End If
If Worksheets("RO").Cells(a, 15).Value = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = False
End If
Next

End Sub

Private Sub CommandButton3_Click()

For a = 3 To 500

If Worksheets("RO").Cells(a, 15).Value = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = False
End If
If Worksheets("RO").Cells(a, 15).Value = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = False
End If
Next

End Sub

Is there any other way out (code) that can provide the desired result a bit fast or can we
change the code to make it work fast

Any help would be appreciated

Regards,

Humayun

 

[jmacleary]

Hi there.

Assuming not all 500 rows are in use, you can set the loop to just check active rows. Also, by turning off screen updating, it will run faster. Your CommandButton3_Click code would then look like this:

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False
lastrow = Worksheets("RO").Cells(Rows.Count, 1).End(xlUp).Row

For a = 3 To lastrow

If Worksheets("RO").Cells(a, 15).Value = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = False
End If
If Worksheets("RO").Cells(a, 15).Value = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = False
End If
Next
Application.ScreenUpdating = True
End Sub

 

[jmacleary]

I should point out that that code will only give the correct last row if all the rows are unhidden, but adding the application.screenupdating will make it much quicker anyway, so you could leave it as 3 to 500

 

[offthelip]

One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.
Unfortunately in your case where you want to hide and unhide rows we do have access the worksheet in a loop, but we can eliminate testing the cell for shipped or in progress from the loop which should halve the time or so.
Thry this:

inarr = Range(Cells(1, 15), Cells(500, 15))

For a = 3 To 500


If inarr(a, 1) = "Shipped" Then
Worksheets("RO").Rows(a).Hidden = False
End If
If inarr(a, 1) = "In Progress" Then
Worksheets("RO").Rows(a).Hidden = True
End If
Next a

 

[hrayani]

Thanks jmacleary, for the code.

I tried the code you provided but its taking same time as before. Only difference is i cannot see the
screen updating - understandably that you have set it to false.

 

[hrayani]

Thanks offthelip for code,

Its working but taking same or more time i would say....

Mine one is the fastest

Lets see if any friend has a solution to it...

 

[Peter_SSs]

Unfortunately in your case .. we do have access the worksheet in a loop ..

I disagree - see below


hrayani,
Give this a try in a copy of your workbook.
Timing will depend on hardware, what else you have in the sheet (eg formulas) etc., but for me this was more than 10 times faster.
Assumes column Z can be used as a helper. If not choose any free column.

Private Sub CommandButton1_Click()
  Dim Data As Variant, b As Variant
  Dim i As Long
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  With Worksheets("RO")
    Data = .Range("O3:O500").Value
    ReDim b(1 To UBound(Data), 1 To 1)
    For i = 1 To UBound(Data)
      Select Case LCase(Data(i, 1))
        Case "shipped": b(i, 1) = 9
        Case "in progress": b(i, 1) = "x"
      End Select
    Next i
    With .Range("Z3").Resize(UBound(b))
      .Value = b
      On Error Resume Next
      .SpecialCells(xlConstants, xlNumbers).EntireRow.Hidden = False
      .SpecialCells(xlConstants, xlTextValues).EntireRow.Hidden = True
      On Error GoTo 0
      .ClearContents
    End With
  End With
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

 

[hrayani]

Thanks Peter,

I tried the code but its debugging with this part

Value = b

of the code highlighting.

Assumes column Z can be used as a helper. If not choose any free column

sorry i did not get it ???? Do i have to do anything in this Column ?
My Data ends at Column O (In Progress/Shipped)

 

[Peter_SSs]

I tried the code but its debugging with this part of the code highlighting.

Value = b

Have you accidentally deleted the "." from before the word Value?

sorry i did not get it ???? Do i have to do anything in this Column ?
My Data ends at Column O (In Progress/Shipped)

If your data ends at column O you don't have to do anything about that part of the code.

 

[dmt32]

as another attempt try following:


In Standard Module

Sub ShowRows(ByVal Shipped As Boolean, ByVal InProgress As Boolean)
    Dim LastRow As Long, a As Long
    On Error GoTo myerror
    With Application
        .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlCalculationManual
    End With
    With ThisWorkbook.Worksheets("RO")
            .Cells.Rows.Hidden = False
        LastRow = .Cells(.Rows.Count, 15).End(xlUp).Row
        For a = 3 To LastRow
            .Cells(a, 1).EntireRow.Hidden = IIf(.Cells(a, 15).Value = "Shipped", Shipped, _
                                            IIf(.Cells(a, 15).Value = "In Progress", InProgress, False))
        Next
    End With
myerror:
    With Application
        .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlCalculationAutomatic
    End With
End Sub

Your Sheet CommandButton Codes

Private Sub CommandButton1_Click()


    ShowRows Shipped:=False, InProgress:=True


End Sub


Private Sub CommandButton2_Click()


    ShowRows Shipped:=True, InProgress:=False


End Sub


Private Sub CommandButton3_Click()


    ShowRows Shipped:=False, InProgress:=False


End Sub

Code Not tested as on granddad duty today but hopefully, may help you

Dave