implementing filter in a dynamic range in Excel VBA

1
votes

I have searched for this and found answers related to filters, and dynamic ranges separately, but could not figure how to put them together.

The structure of my excel sheet is:

  • Data:

    • Date A B C
    • 02-02-2012 a1 b1 c1
    • 07-07-2012 a2 b2 c2

  • Graphs:

    • graphs generated on this sheet

I am using a named dynamic range myDataRange that returns only those rows whose data is equal to or greater than a particular reference date:

  • =OFFSET(Data!$C$2,COUNTIF(Data!$B:$B,"<"&Graphs!$B$1&""),0,(COUNTA(Data!$C:$C)-1-COUNTIF(Data!$B:$B,"<"&Graphs!$B$1&"")),1)

On the graphs sheet I have entered a date in cell B1, for example : 05-05-2012

This would ensure myDataRange only returns the second row of data (whos date is 07-07-2012). Out of this, I plot the column C.

Question: But now I also need the dynamic range to be filtered as per Column A. So if reference date is 05-05-2012 in graphs cell B1 and filter is a2, then myData range returns only one row. But if filter is 05-05-2012 and a1, then it would return 0 rows.

Could someone pls help with this - can I modify the dynamic range itself or would I have to implement this another way? I have tried to modify the dynamic range parameters, but I keep getting error messages, as it returns no values and then Excel can't graph it. Thanks in advance :)

1
vbaexcelexcel-2010
Have you tried recording a Macro for Autofiltering? :) - bonCodigo
@bonCodigo - hi, thanks for the suggestion. did not know about it. will look into it right now :) - xcentaur
@bonCodigo - looks like Autofilter might not work. but found an article on Advanced filter that might help with the date comparison. - xcentaur
@bonCodigo - silly me, Autofilter worked great. Thanks so much! :) - xcentaur
And if you are interested in please take a look at this as well. It is just doing a sort and immitate the autofilter but doesn't hide the rows. IF you are interested in the future :) - bonCodigo

1 Answers

2
votes

I am using the range mentioned above, and the code below on a combobox I inserted on the sheet.

here is the code:

Sub DropDown2_Change()

Dim selectedVal As String

With ThisWorkbook.Sheets("Graphs02").Shapes("Drop Down 2").ControlFormat
selectedVal = .List(.Value)
End With

'MsgBox selectedVal

With ThisWorkbook.Sheets("Data")
.Select
.Range("A1:BY60000").Select

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=selectedVal

End With

ThisWorkbook.Sheets("Graphs02").Select

End Sub

in this, the sheet on which I have put in the combobox (Drop Down 2) is sheet "graphs02". The sheet Data has all my data, which is filtered according to the value selected in the combobox.

Again, I got most of this working with help of stackoverflow :)