Bernie Deitrick
12/12/2006 6:23:00 PM
The solution to this problem was
Sub FilterBasedOnActiveCellContents()
ActiveCell.CurrentRegion.AutoFilter _
Field:=ActiveCell.Column - ActiveCell.CurrentRegion.Cells(1).Column + 1, _
Criteria1:=ActiveCell.Text
End Sub
It turns out that the values being filtered were numbers, with different formatting, and filtering
is based on formatted values, not the underlying values.
HTH,
Bernie
MS Excel MVP
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:us8fyShHHHA.1264@TK2MSFTNGP03.phx.gbl...
> Robin,
>
> I'll be on the lookout - but no file yet....
>
> Bernie
> MS Excel MVP
>
>
> "Cheekyaardvark" <robinraw@hotmail.com> wrote in message
> news:1165944572.798948.302060@16g2000cwy.googlegroups.com...
>> Bernie...
>>
>> Sorry for being unclear...
>>
>> This macro still seems to hold the value of the initial filter.. I am
>> not explaining very well, I have sent you a sample sheet, which
>> hopefully helps you to help me
>>
>> Robin
>>
>>
>> Bernie Deitrick wrote:
>>> Robin,
>>>
>>> The first macro will filter based on the first value, then the next cell value, then the next.
>>> But
>>> you can use this new macro when you want to clear the original filtering before filtering on one
>>> column only.
>>>
>>> Sub ReFilterBasedOnActiveCellContents()
>>> If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
>>> ActiveCell.CurrentRegion.AutoFilter _
>>> Field:=ActiveCell.Column - ActiveCell.CurrentRegion.Cells(1).Column + 1, _
>>> Criteria1:=ActiveCell.Value
>>> End Sub
>>>
>>> It wasn't clear from your post which type of filtering you prefered.
>>>
>>> HTH,
>>> Bernie
>>> MS Excel MVP
>>>
>>>
>>> "Cheekyaardvark" <robinraw@hotmail.com> wrote in message
>>> news:1165940906.022381.62700@16g2000cwy.googlegroups.com...
>>> > Thanks Bernie
>>> >
>>> > Does this macro define the current cell as the active cell... the macro
>>> > only works the first time & seems to capture the value of that first
>>> > run for it to do subsequent filters on... for example if I run this on
>>> > a cell with 0.00 value & then move to a different column it attempts to
>>> > filter on 0.00 not the new active cell
>>> > What I was trying to get to was a position where I can filter on a
>>> > value in one column & then move to another column & further filter on
>>> > that to "home in" on fewer & fewer rows,
>>> > I want to run subsequent runs of the same macro but with the criteria
>>> > defined in the active cell
>>> >
>>> > Thanks again for the assistance...
>>> >
>>> > Robin
>>> >
>>> >
>>> > Bernie Deitrick wrote:
>>> >> Robin,
>>> >>
>>> >> This is written with the assumption that you have only one contiguous data table filtered:
>>> >>
>>> >> Sub FilterBasedOnActiveCellContents()
>>> >> ActiveCell.CurrentRegion.AutoFilter _
>>> >> Field:=ActiveCell.Column - ActiveCell.CurrentRegion.Cells(1).Column + 1, _
>>> >> Criteria1:=ActiveCell.Value
>>> >> End Sub
>>> >>
>>> >> HTH,
>>> >> Bernie
>>> >> MS Excel MVP
>>> >>
>>> >>
>>> >> "Cheekyaardvark" <robinraw@hotmail.com> wrote in message
>>> >> news:1165936560.774837.256690@80g2000cwy.googlegroups.com...
>>> >> >I have a spreadsheet that I already have autofilter activated on, what
>>> >> > I would like to do is have a macro that I can pop on my toolbar that
>>> >> > will filter the file based on the active cell contents, I had a macro
>>> >> > that did this in previous verison of excel but I somehow lost it during
>>> >> > migration to excel 2003 & cannot recreate it
>>> >> >
>>> >> >
>>> >> > Can anybody guide me on this
>>> >> >
>>> >> > Appreciate it
>>> >> >
>>> >> > Robin
>>> >> >
>>> >
>>
>
>