Mar 30 2021 05:27 AM
I am looking to use macro's to create a function that will hide selected rows in a sheet based on the entry of a drop down field, I haven't really had much experience with this sort of thing before.
The solution will be used in an excel dashboard tracking various metrics, and having this automatic filter will make using the workbook easier.
I've included a document which has an example of what I am working with and will hopefully help with a solution..
Mar 30 2021 05:42 AM
See the attached version - it's now a .xlsm workbook since it contains VBA code.
To inspect the code, right-click the sheet tab and select 'View Code' from the context menu.
Mar 30 2021 06:54 AM - edited Mar 30 2021 06:55 AM
I have copied the code to my real world example, changed the cell referencing the drop down list and receive the following message;
showalldata method of worksheet class failed
Any ideas?
Mar 30 2021 07:06 AM
Does it work better if you change the code in the worksheet module to
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitHere
If Not Intersect(Range("A2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("A2").Value = "" Then
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Else
Range("A5").CurrentRegion.AutoFilter Field:=2, Criteria1:=Range("A2").Value
End If
End If
ExitHere:
Application.EnableEvents = True
End Sub