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

 

[hrayani]

hey peter.... i am so so sorry

forgot to tell you that i change the case of "shipped" and "in progress" in the code

i mean i changed that to proper case (1st alphabet Capital)

Let me change them back to the original and will update you shortly

 

[hrayani]

yes it was my mistake change the letter to uppercase

now its working like a bullet.... wow...

but what for the next two codes... shall we be using the same with a bit of amendment ??

 

[Peter_SSs]

forgot to tell you that i change the case of "shipped" and "in progress" in the code

Hmm...

No i have not changed anything else in the code

 

[hrayani]

Got it peter... For all Three

Changed the true false part of the code...

Thanks for you time .... I really appreciate

Your cord works like a bullet as compared to mine

Regards,

Humayun

 

[Peter_SSs]

yes it was my mistake change the letter to uppercase

now its working like a bullet.... wow...

That is more what I was expecting.

but what for the next two codes... shall we be using the same with a bit of amendment ??

I will look at them.

Edit: I hadn't seen your last post so you have it all working now I think.

 

[hrayani]

Hmm...

Originally Posted by hrayani



No i have not changed anything else in the code

I apologize once again...

 

[hrayani]

I hadn't seen your last post so you have it all working now I think.

yes, its all working fine now

 

[Peter_SSs]

Got it peter... For all Three

Changed the true false part of the code...

As you have noticed, all the codes are basically the same apart from the True/false parts, so this is what I would actually do as it would make it easier for any future tweaks that might be needed.

Put this in a standard module. Note that it is not a Command Button Click code, but is basically the code we had apart from the blue bits.

Sub Show_Hide(bHideShipped As Boolean, bHideInProgress As Boolean)
  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 = bHideShipped
      .SpecialCells(xlConstants, xlTextValues).EntireRow.Hidden = bHideInProgress
      On Error GoTo 0
      .ClearContents
    End With
  End With
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

.. and change the three button click codes to these so that they are all using the one code with the show/hide parameters passed to it

Private Sub CommandButton1_Click()
  Show_Hide bHideShipped:=False, bHideInProgress:=True
End Sub

Private Sub CommandButton2_Click()
  Show_Hide bHideShipped:=True, bHideInProgress:=False
End Sub

Private Sub CommandButton3_Click()
  Show_Hide bHideShipped:=False, bHideInProgress:=False
End Sub

 

[hrayani]

Hello Peter,

Tried the code you last provided... Changed In Proress to In Process

Everything is working just PERFECT......

I just wanted to ask you that i noticed this part in the code

Application.ScreenUpdating = False

and applied it to my other macro/recordings as well and it made run my
macros/recordings much faster with not much blinking on the screen.

I will show you one of my macro/recording

I was using the below code which was running a bit slow.... not that slow but still a bit slow as compared to the new one which has the screenupdating set to False

Previous

Sub weekly_summary_two_po()    
   
    Rows("16:86").Select
    Selection.EntireRow.Hidden = False
    Range("29:52,66:85").Select
    Selection.EntireRow.Hidden = True
    Range("L4").Select
            
End Sub

New

Sub weekly_summary_two_po()
    
    Application.ScreenUpdating = False
    Rows("16:86").Select
    Selection.EntireRow.Hidden = False
    Range("29:52,66:85").Select
    Selection.EntireRow.Hidden = True
    Range("L4").Select
            
End Sub

I just wanted to ask you is it Ok to do it or i should not be doing it.... ??

Regards,

Humayun

 

[Peter_SSs]

I just wanted to ask you is it Ok to do it or i should not be doing it.... ??

In general, "do it". In most cases it makes less screen flicker and faster code execution.