• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique Sorted Sub-Categories used within the date criteria

grk

New Member
I have an excel check register with sub-categories and categories that I need to know the sorted unique sub-categories by the calendar year. I'm currently using formulas but that's proven to run my CPU into the ground. So I did a bit of research and found some VB code on this site but I'm not able to get I to work as I need. If you open the attached file and take a look at what I have;
Here are the columns;
DateDESCRIPTIONAMOUNTNOTESub-CategoryCategory
1/2/2015​
CC:MiscDeposit
1/5/2015​
CC:MiscDeposit
1/5/2015​
CC:MiscDeposit
1/5/2015​
CC:1st DataCC Fees

Here is what I'm trying to accomplish;
Unique Sorted Sub-Categories used within the date criteria
1/1/2015​
1/1/2015​
1/1/2016​
1/1/2017​
1/1/2018​
1/1/2019​
1/1/2020​
12/31/2020​
12/31/2015​
12/31/2016​
12/31/2017​
12/31/2018​
12/31/2019​
12/31/2020​

I'm certain someone can get me past this.
If I've left out any needed information please let me know.
Thank you
 

Attachments

  • Sub-Categories.xlsm
    62.2 KB · Views: 2
I've attached the file with the expected result I get with formulas but formulas take the CPU and run it for several minutes. This is why I am looking for a VBA solution.
 

Attachments

  • Sub-Categories.xlsm
    64.1 KB · Views: 3
According to your last attachment a VBA beginner starter demonstration :​
Code:
Sub Demo1()
             Dim Rc As Range
             Application.ScreenUpdating = False
    With Sheet28.[A3].CurrentRegion
        For Each Rc In .Parent.[H3:N3]
                .Columns(1).AutoFilter 1, ">=" & Rc(0).Value2, xlAnd, "<=" & Rc.Value2
                .Columns(5).Offset(1).Copy Rc(2)
                .Cells(1).AutoFilter
            With Range(Rc, Rc.End(xlDown))
                .RemoveDuplicates 1, xlYes
                .Sort Rc, xlAscending, Header:=xlYes
            End With
        Next
            .Parent.[H2].CurrentRegion.Columns.AutoFit
    End With
             Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
  • Like
Reactions: grk
Back
Top