| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Filter on active cell within autofilter
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Filter on active cell within autofilter
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" wrote in message ups.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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Filter on active cell within autofilter
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" wrote in message ups.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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Filter on active cell within autofilter
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" wrote in message ps.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" wrote in message ups.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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Filter on active cell within autofilter
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" wrote in message ps.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" wrote in message ups.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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Filter on active cell within autofilter
Robin,
I'll be on the lookout - but no file yet.... Bernie MS Excel MVP "Cheekyaardvark" wrote in message ups.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" wrote in message ps.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" wrote in message ups.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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Filter on active cell within autofilter
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 ... Robin, I'll be on the lookout - but no file yet.... Bernie MS Excel MVP "Cheekyaardvark" wrote in message ups.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" wrote in message ps.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" wrote in message ups.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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I filter for a blank cell, using custom autofilter | Excel Discussion (Misc queries) | |||
| Active Cell in Auto Filter | Excel Discussion (Misc queries) | |||
| Active Cell to be used as filter... | Excel Programming | |||
| Macro to paste in the active cell the contents of a cell from another file?? | Excel Programming | |||
| autofilter on a not-active sheet | Excel Programming | |||
Linear Mode
