Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
hi all dears
i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
Try this way:
Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
Thank you for reply
what value i put in cell "A1"? "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
HI Stefi
it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
You are welcome! Thanks for the feedback!
Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
HI Stefi
when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
The VBA equivalent of choosing All from Autofilter dropdown list is:
Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
hi stefi
Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
Insert this event sub into code window of Sheet2:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "A1" Then Call filtFcol(Target.Value) End Sub Insert this sub into a normal module: Sub filtFcol(filtval) Worksheets("Sheet1").Select Columns("F:F").Select Selection.AutoFilter If filtval = "all" Then Selection.AutoFilter Field:=1 Else Selection.AutoFilter Field:=1, Criteria1:=filtval End If End Sub Regards, Stefi €žnader€ť ezt Ă*rta: hi stefi Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
hi
i really appreciate your helps, i am new in excel, specially in VB and i will spent hours trying to use your helps. i hop let me i use your helps about this sobject at the next times. warm regards, nader "Stefi" wrote: Insert this event sub into code window of Sheet2: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "A1" Then Call filtFcol(Target.Value) End Sub Insert this sub into a normal module: Sub filtFcol(filtval) Worksheets("Sheet1").Select Columns("F:F").Select Selection.AutoFilter If filtval = "all" Then Selection.AutoFilter Field:=1 Else Selection.AutoFilter Field:=1, Criteria1:=filtval End If End Sub Regards, Stefi €žnader€ť ezt Ă*rta: hi stefi Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
You are welcome! Thanks for the feedback! Feel free to post if you need some help to understand the code! Stefi €žnader€ť ezt Ă*rta: hi i really appreciate your helps, i am new in excel, specially in VB and i will spent hours trying to use your helps. i hop let me i use your helps about this sobject at the next times. warm regards, nader "Stefi" wrote: Insert this event sub into code window of Sheet2: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "A1" Then Call filtFcol(Target.Value) End Sub Insert this sub into a normal module: Sub filtFcol(filtval) Worksheets("Sheet1").Select Columns("F:F").Select Selection.AutoFilter If filtval = "all" Then Selection.AutoFilter Field:=1 Else Selection.AutoFilter Field:=1, Criteria1:=filtval End If End Sub Regards, Stefi €žnader€ť ezt Ă*rta: hi stefi Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
hi
I did:click right on sheet2 tab---view code---inserted your suggestion (event sub) in to the window. then I got a new module and inserted your suggestion (sub) in to that window. because of the name of this project didn't show in the ' run macro' window, so I don't know how I running this project. kind regards. nader "Stefi" wrote: You are welcome! Thanks for the feedback! Feel free to post if you need some help to understand the code! Stefi €žnader€ť ezt Ă*rta: hi i really appreciate your helps, i am new in excel, specially in VB and i will spent hours trying to use your helps. i hop let me i use your helps about this sobject at the next times. warm regards, nader "Stefi" wrote: Insert this event sub into code window of Sheet2: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "A1" Then Call filtFcol(Target.Value) End Sub Insert this sub into a normal module: Sub filtFcol(filtval) Worksheets("Sheet1").Select Columns("F:F").Select Selection.AutoFilter If filtval = "all" Then Selection.AutoFilter Field:=1 Else Selection.AutoFilter Field:=1, Criteria1:=filtval End If End Sub Regards, Stefi €žnader€ť ezt Ă*rta: hi stefi Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
Just type a new value in cell A1 in sheet2, the event sub should
automatically fire (if the installation was correct). Stefi €žnader€ť ezt Ă*rta: hi I did:click right on sheet2 tab---view code---inserted your suggestion (event sub) in to the window. then I got a new module and inserted your suggestion (sub) in to that window. because of the name of this project didn't show in the ' run macro' window, so I don't know how I running this project. kind regards. nader "Stefi" wrote: You are welcome! Thanks for the feedback! Feel free to post if you need some help to understand the code! Stefi €žnader€ť ezt Ă*rta: hi i really appreciate your helps, i am new in excel, specially in VB and i will spent hours trying to use your helps. i hop let me i use your helps about this sobject at the next times. warm regards, nader "Stefi" wrote: Insert this event sub into code window of Sheet2: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "A1" Then Call filtFcol(Target.Value) End Sub Insert this sub into a normal module: Sub filtFcol(filtval) Worksheets("Sheet1").Select Columns("F:F").Select Selection.AutoFilter If filtval = "all" Then Selection.AutoFilter Field:=1 Else Selection.AutoFilter Field:=1, Criteria1:=filtval End If End Sub Regards, Stefi €žnader€ť ezt Ă*rta: hi stefi Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
just i changed:
filtFcol to filtHcol (F:F) to (H:H) A1 to I3 sheet1 to correct sheet name as a my work book possition; does it make mistake? regards nader "Stefi" wrote: Just type a new value in cell A1 in sheet2, the event sub should automatically fire (if the installation was correct). Stefi €žnader€ť ezt Ă*rta: hi I did:click right on sheet2 tab---view code---inserted your suggestion (event sub) in to the window. then I got a new module and inserted your suggestion (sub) in to that window. because of the name of this project didn't show in the ' run macro' window, so I don't know how I running this project. kind regards. nader "Stefi" wrote: You are welcome! Thanks for the feedback! Feel free to post if you need some help to understand the code! Stefi €žnader€ť ezt Ă*rta: hi i really appreciate your helps, i am new in excel, specially in VB and i will spent hours trying to use your helps. i hop let me i use your helps about this sobject at the next times. warm regards, nader "Stefi" wrote: Insert this event sub into code window of Sheet2: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "A1" Then Call filtFcol(Target.Value) End Sub Insert this sub into a normal module: Sub filtFcol(filtval) Worksheets("Sheet1").Select Columns("F:F").Select Selection.AutoFilter If filtval = "all" Then Selection.AutoFilter Field:=1 Else Selection.AutoFilter Field:=1, Criteria1:=filtval End If End Sub Regards, Stefi €žnader€ť ezt Ă*rta: hi stefi Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aouto filter by macro
HI stefi
I tried again whit your VB without changes but it returned a warning message:(sub or function not defined) and (call filtFcol)in the code window was highlight. please help me best regards. nader "Stefi" wrote: Just type a new value in cell A1 in sheet2, the event sub should automatically fire (if the installation was correct). Stefi €žnader€ť ezt Ă*rta: hi I did:click right on sheet2 tab---view code---inserted your suggestion (event sub) in to the window. then I got a new module and inserted your suggestion (sub) in to that window. because of the name of this project didn't show in the ' run macro' window, so I don't know how I running this project. kind regards. nader "Stefi" wrote: You are welcome! Thanks for the feedback! Feel free to post if you need some help to understand the code! Stefi €žnader€ť ezt Ă*rta: hi i really appreciate your helps, i am new in excel, specially in VB and i will spent hours trying to use your helps. i hop let me i use your helps about this sobject at the next times. warm regards, nader "Stefi" wrote: Insert this event sub into code window of Sheet2: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "A1" Then Call filtFcol(Target.Value) End Sub Insert this sub into a normal module: Sub filtFcol(filtval) Worksheets("Sheet1").Select Columns("F:F").Select Selection.AutoFilter If filtval = "all" Then Selection.AutoFilter Field:=1 Else Selection.AutoFilter Field:=1, Criteria1:=filtval End If End Sub Regards, Stefi €žnader€ť ezt Ă*rta: hi stefi Description of my question is: I have a dropdown list like: toyota nissan kia all in "sheet2" cell "A1" and i have column F in "sheet1" contain names above. I want to have a macro, when i choose a value from dropdown list in "sheet2" "A1" and apply macro, Excell filters in "sheet1" column F by this value and when i choose "all" value in dropdown list and apply this macro again, it unhides all cells, including blanks. kind regards, nader "Stefi" wrote: The VBA equivalent of choosing All from Autofilter dropdown list is: Selection.AutoFilter Field:=6 It must unhide all cells, including blanks. If you have other problem, give more details! Regards, Stefi €žnader€ť ezt Ă*rta: HI Stefi when i filter(all) by your macro it don't show blanks. is possible it show blanks too? it is important for me. thanks "Stefi" wrote: You are welcome! Thanks for the feedback! Stefi €žnader€ť ezt Ă*rta: HI Stefi it works well. any thanks. nader "Stefi" wrote: Try this way: Selection.AutoFilter Field:=6, _ Criteria1:="=" & Sheets("Sheet2").Range("A1").Value Regards, Stefi €žnader€ť ezt Ă*rta: hi all dears i need your helps I use following mocro: Sheets("Sheet1").Select Selection.AutoFilter Field:=6, _ Criteria1:="=" & Range("Sheet2!A1").Value I need to filter(All) by putting a value in cell ("Sheet2!A1"). Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter date in macro | Excel Discussion (Misc queries) | |||
how can this filter be done in a macro? | Excel Discussion (Misc queries) | |||
Do i use a filter or a macro? | Excel Discussion (Misc queries) | |||
macro to filter out data | Excel Worksheet Functions | |||
Need a filter macro | Excel Discussion (Misc queries) |