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]

Try this

Sub GetDate2()

Dim i As Long
Dim c As Range, rng As Range, rngDel As Range

Application.ScreenUpdating = False

Set rng = Sheet1.Range("A3:A1828")

rng.EntireRow.Hidden = False
For Each c In rng
    If c.Value > Sheet2.Range("A1").Value Then
        If Not rngDel Is Nothing Then
            Set rngDel = Application.Union(c, rngDel)
        Else
            Set rngDel = c
        End If
    End If
Next
        
rngDel.EntireRow.Hidden = True

End Sub

 

[bebo021999]

"End If" was missing.
Also try to run from 3 to 1828

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

 

[Peter_SSs]

Does this do what you want?

Sub GetDate2a()
  Range("A2:A1828").AutoFilter Field:=1, Criteria1:="<=" & CLng(DateValue(Sheet2.Range("A1").Value))
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[footoo]

Maybe :

Sub v()
Dim rng As Range
[A3:A1828].EntireRow.Hidden = False
Set rng = [A3:A1828].Find(Sheet2.[A1])(0)
If Not rng Is Nothing Then Range("A3:A" & rng.Row).EntireRow.Hidden = True
End Sub

 

[shaju]

Thanks a lot for the replies. Now in "travelling mode". Shall come back after I try these in my file.

 

[shaju]

Thanks A Lot Zot, bebo021999, Peter_SSs and footoo for looking into this matter and providing your insights, which will surely help me studying new approaches.
Decided to go by the code of Peter_SSs as it is the fastest and at the same time less lines of code.
The codes of Zot and footoo also were fast, slower than Peter_Sss's code by just milliseconds.
bebo021999's code is the same as my code, but it does job from row 3 downwards whereas my code does it from the last row upwards. Both took almost the same time ...around 28-30 seconds. I wanted to cut down the time.
Once again Thanks to all.

 

[Peter_SSs]

Once again Thanks to all.

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

 

[shaju]

Thank You Peter for the additional code. Still clinging on to the first code as it has fewer lines.
Using Filter option for this was quite a new approach to me.
This group really rocks.

 

[Peter_SSs]

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