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

 

[Peter_SSs]

Forgot to mention: If the dot before Value is not the problem, please post the full error message.

 

[hrayani]

Thanks Dave,

I tried the code you provided

Taking more or less same time

 

[hrayani]

Forgot to mention: If the dot before Value is not the problem, please post the full error message.

Now i tied it with dot and without dot.... This time no message is there... i mean nothing is happening ...
in both cases (with / without dot) its the same

 

[dmt32]

Thanks Dave,

I tried the code you provided

Taking more or less same time

no worry just an idea - hopefully someone here will resolve for you

Dave

 

[Peter_SSs]

Now i tied it with dot and without dot.... This time no message is there... i mean nothing is happening ...
in both cases (with / without dot) its the same

The dot must be there!

Are you sure the code hasn't just run so fast you didn't notice anything happening?

Remove the line of code near the bottom that says

.ClearContents

& run the code again. have a look at column Z and see if there are any 9's or x's visible.

Have you changed anything else in the code?

 

[hrayani]

Yes Dave,

But would like one thing to share which i experienced with Peter's code and that is when i copied the code for the first time and when i got back to the sheet where the command buttons are. What i noticed is when i pressed the command button 2 and 3 which were running the codes mentioned in post # 1. they were working really fast (strange......) as peter mentioned. but when i pressed the 1st command button which had the peter code - then it gave the debugging message and suddenly everything stops.

After that as i mentioned in post # 13 that i tried the code with / without dots then the code dose not do anything.

 

[Peter_SSs]

But would like one thing to share which i experienced with Peter's code and that is when i copied the code for the first time and when i got back to the sheet where the command buttons are. What i noticed is when i pressed the command button 2 and 3 which were running the codes mentioned in post # 1. they were working really fast (strange......) as peter mentioned. but when i pressed the 1st command button which had the peter code - then it gave the debugging message and suddenly everything stops.

After that as i mentioned in post # 13 that i tried the code with / without dots then the code dose not do anything.

It sounds a bit like you may not have connected the Command buttons to the codes correctly.

As another test, instead of using the command button with my code, find my code in the vba window & click in it anywhere then press F5. Now go back to your 'RO' sheet and see if the rows have been hidden/unhidden.

 

[hrayani]

The dot must be there!

yes it is there

Are you sure the code hasn't just run so fast you didn't notice anything happening?

No brother its not like that.... i am noticing and nothing is happening.

Remove the line of code near the bottom that saysCode:
.ClearContents

Ok... Removed

& run the code again. have a look at column Z and see if there are any 9's or x's visible.

I ran the code again. There are no 9's or x's in column Z

Have you changed anything else in the code?

No i have not changed anything else in the code

 

[Peter_SSs]

OK, so next is the test at the end of post 17

 

[hrayani]

It sounds a bit like you may not have connected the Command buttons to the codes correctly.

As another test, instead of using the command button with my code, find my code in the vba window & click in it anywhere then press F5. Now go back to your 'RO' sheet and see if the rows have been hidden/unhidden.

Tried this also... nothing happens... Tried the F5 with my other 2 codes which are connected with command buttons 2 & 3. they work fine.

Also made a new button # 4 and attached your code to that also... Still nothing happens