Change the code to run faster

[shaju]

Hi,
I have an excel file in which one row is for one date. The dates are in column A in descending order ie A3= 31-12-2025, A4=30-12-2025, A5=29-12-2025...etc waybackwards till 01-01-2021. I am using this code to hide rows which have date greater than current date. It works, but it takes a long time to complete. How can this be rewritten so that this code runs faster. The code is placed in the Worksheet_Activate event. (Sheet2.Range("A1").Value will always be the current date.)

Sub GetDate2()
Sheet1.Range("A3:A1828").Select
Selection.EntireRow.Hidden = False
Dim i As Long
For i = 1828 To 3 Step -1
If Sheet1.Range("A" & i).Value > Sheet2.Range("A1").Value Then Sheet1.Range("A" & i).EntireRow.Hidden = True
Next i
End Sub

TIA

 

[Zot]

I'm not suggesting that you should change from the AutoFilter code, but don't fall into the trap of choosing code because it is shorter. You need to choose code based on what works best for you in terms of things like
- how well it does what you want
- how efficiently it does that (that's why I made the extra suggestion as I thought, without knowing exactly what else is in your worksheet, perhaps it might be faster?)
- how flexible it is
- how well you understand it if you might need to amend it yourself later
- etc

I always tend to forget that there are many functions in Excel that can simplify coding. ?

 

[shaju]

You're welcome. Glad we could help.

I don't know how you use the worksheet but one of the advantages of AutoFilter to my mind is the easy access via the drop-down to re-show the hidden rows.
However, if that is not an issue and the data is as described in post 1 then this would also be another option you could test.

Sub GetDate2b()
  Application.ScreenUpdating = False
  Rows("3:1828").Hidden = False
  Rows("3").Resize(Range("A3").Value - Date).Hidden = True
  Application.ScreenUpdating = True
End Sub

Can you please describe what happens in the 3rd line ;
Rows("3").Resize(Range("A3").Value - Date).Hidden = True
TIA

 

[shaju]

I always tend to forget that there are many functions in Excel that can simplify coding. ?

That is the beauty of this group. You get better "technologies" which are useful in future.

 

[Peter_SSs]

Can you please describe what happens in the 3rd line ;
Rows("3").Resize(Range("A3").Value - Date).Hidden = True

When that code gets to that line, all rows are visible. Your dates are sequential and in descending order.
To make the explanation easier, let's suppose that A3 has the date 9 Nov 2021. That would mean that today's date (6 Nov 2021 for me at the moment) would be in A6.
'Date' in vba is the equivalent of the worksheet function Today() so Date holds 6 Nov 2021.
Therefore
Range("A3").Value - Date
is
9 Nov 2021 - 6 Nov 2021 = 3

So Rows("3").Resize(Range("A3").Value - Date).Hidden = True
becomes
Rows("3").Resize(3).Hidden = True
which becomes
Rows("3:5").Hidden = True
which leaves row 6 as the first visible row of dates and since A6 holds today's date that is the first date visible.

 

[shaju]

When that code gets to that line, all rows are visible. Your dates are sequential and in descending order.
To make the explanation easier, let's suppose that A3 has the date 9 Nov 2021. That would mean that today's date (6 Nov 2021 for me at the moment) would be in A6.
'Date' in vba is the equivalent of the worksheet function Today() so Date holds 6 Nov 2021.
Therefore
Range("A3").Value - Date
is
9 Nov 2021 - 6 Nov 2021 = 3

So Rows("3").Resize(Range("A3").Value - Date).Hidden = True
becomes
Rows("3").Resize(3).Hidden = True
which becomes
Rows("3:5").Hidden = True
which leaves row 6 as the first visible row of dates and since A6 holds today's date that is the first date visible.

That was an excellent explanation. Thank you for making it clear using my data itself.
Addressing cells in the manner Rows("3:5") also is a new info to me. Thanks for giving that insight too.

 

[Peter_SSs]

Glad it helped.