Excel Autofilter and Criteria Functional using Visual Basic

Evening

Can someone help me out please.  I have been out of game for a while now and things just seem to have gone striaght out of my head.

I am trying to add a little bit of functionality  inot an excel spreadsheet.  I have attach copy as example.

What I am trying to achieve is the ability to add an automatic autofilter function on row 3 for the available cells.  This is then supposed currently by using radio buttons to sort designated columns by either ASC or DESC and in turn hiding unwanted columns.  Currently autofilter already in place but looking at adding button click function and calling autofilter in place

i.e oExcelWorkSheet.Range("B3").AutoFilter

to simplify rather then going into menu tollbar.

Radio buttons when pressed run designated function, hide not required columns and sort as per label suggests.

I have attached code currently using at momnet but having major problems with the autofilter part.  If I want to implement autofilter into button click event before the option button click event is taken place what changes do I need.  My over option was to use check boxes and a command button labelled filter which called events filter event currently assigned to under each option button function.  This forcast is only going to get larger with more functionality over time including advanced filtering and subtotals with more advanced filter criteria but for time being basic filtering is a must.

Can anyone help me out my head is battered.  Not used VB for years

The top Sub is function just added to apply autofilter using macro rathere than menu toolbar options
Sub ApplyAutoFilters()

If ActiveSheet.AutoFilterMode = True Then

ActiveSheet.AutoFilterMode = False

Else

With ActiveSheet

            .AutoFilterMode = False
            .Range("B3:J3").AutoFilter

    End With
    
End If

End Sub

Private Sub OptionButton1_Click()
'
' ConsReport Asc Macro
'

'
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    Range("J5").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=Range("J5"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Private Sub OptionButton2_Click()
'
' ft2Report Asc Macro
'

'
    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Range("I5").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=Range("I5"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Private Sub OptionButton3_Click()
'
' ConsReport Desc Macro
'

'
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    Range("J5").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=Range("J5"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Private Sub OptionButton4_Click()
'
' ft2Report Desc Macro
'

'
    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Range("I5").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=Range("I5"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

Test-Report.xls
stefone1981Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
I think you had it most of the way... the only thing left was to correctly identify the range of cells that you wanted to sort.

Attached is a modified file.

I added a function that I use in quite a few projects to find that last row/column in a worksheet.
I used the results of that function to specify the sort range for a given radio dial button.

   Dim myLastCell As Range
    Dim mySortRange As Range
    
    Set myLastCell = LastCell(ActiveSheet)
    Set mySortRange = Range("I5", Cells(myLastCell.Row - 2, 9))

---- at this point mySortRange defines the whole column to be sorted....

then I used that range in the sort command...

    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal

Open in new window

Test-Report.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stefone1981Author Commented:
Hi buttersk

Thanks for help.  Not used VB for a while went onto a new language but simple things are now coming back to me.

Only thing think I would need to include is is sub for optionbuttons  is if user has removed autofilter before pressing one of the 4 options.  Nothing worse than a debug pop up box to scare the life out of someone.

What your preference, iF statement and error handling to insert autofilter or maybe easier would be to lock autofilter in place to prevent it being removed.

And thanks for function at bottom simple yet effective, can use it for my print option.
0
Ken ButtersCommented:
I would probably just lock it down
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.