Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter List
Hi,
I have a worksheet the data is setup like so: Building RM # Type Size Building 001 105 4 Building 001 106 101 4 Building 001 108 5 Building 001 107 5 Building 002 201 G 6 Building 003 205 J 6 Building 003 209 B 8 Building 003 121 8 Building 004 124 5 Building 004 123 5 Building 004 1 A 4 When A2 Building is filtered I need to be able to select each criteria one by one and copy the Building and the data from E1 to Worksheet 2 beginning with A2. Can the macro below be modified to do the above? <BEGIN Macro Code Sub AutoFilterList() ' With Selection.AutoFilter .Field = 1 For Each crt In .Criteria1 MsgBox crt Next End With <END Macro Code Thnak you for your help, jfcby |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter List
Hi Don,
Before Example: Worksheet 1 Col 1 Col 2 Col 3 Row1 Bldg Rm # Type Row2 Bldg1 102 6 Row3 Bldg1 105 4 Row4 Bldg2 101 8 Row5 Bldg2 302 6 Row6 Bldg2 501 6 Row7 Bldg3 108 4 Row8 Bldg3 201 6 Row9 Bldg3 309 8 Row10 Bldg3 310 6 After Example: New Worksheet Col 1 Col 2 Row1 Bldg Total Row2 Bldg1 2 Row3 Bldg2 3 Row4 Bldg3 3 Thank you for your help, jfcby |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter List
One way. Assumes that col E:F of the source sheet is available
Sub makeuniquelistandcount() 'make list lr = Cells(Rows.Count, "a").End(xlUp).Row Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("E1"), Unique:=True 'create formulas lr = Cells(Rows.Count, "e").End(xlUp).Row For i = 1 To lr Cells(i, "f").FormulaR1C1 = "=COUNTIF(C[-5],RC[-1])" Next i 'move to new sheet Cells(1, "e").Resize(lr, 2).Cut Sheets.Add ActiveSheet.Paste End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jfcby" wrote in message ... Hi Don, Before Example: Worksheet 1 Col 1 Col 2 Col 3 Row1 Bldg Rm # Type Row2 Bldg1 102 6 Row3 Bldg1 105 4 Row4 Bldg2 101 8 Row5 Bldg2 302 6 Row6 Bldg2 501 6 Row7 Bldg3 108 4 Row8 Bldg3 201 6 Row9 Bldg3 309 8 Row10 Bldg3 310 6 After Example: New Worksheet Col 1 Col 2 Row1 Bldg Total Row2 Bldg1 2 Row3 Bldg2 3 Row4 Bldg3 3 Thank you for your help, jfcby |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter List
Don,
Thank you for your help! How do you specify a header row in the Advance Filter. Because Row One is the header row. When I run the macro from A2 in E2 Bldg 1 is listed twice. How can the macro be modified so that the Advance Filter can begin from A2 and not include Bldg 1 twice? <BEGIN MACRO CODE Sub MakeUniqueListAndCount_1() 'Make Unique List & Count lr = Cells(Rows.Count, "A").End(xlUp).Row Range("A2:A" & lr).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("E2"), Unique:=True 'create formulas lr = Cells(Rows.Count, "E").End(xlUp).Row For i = 1 To lr Cells(i, "F").FormulaR1C1 = "=COUNTIF(C[-5],RC[-1])" Next i 'move to new sheet Cells(1, "E").Resize(lr, 2).Cut Sheets.Add ActiveSheet.Paste End Sub <END MACRO CODE Thank for your help, jfcby |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Filter List
Advance filter needs a header row so run it from a1 as I did and you will
get: Bldg 1 Bldg1 2 Bldg2 3 Bldg3 4 -- Don Guillett Microsoft MVP Excel SalesAid Software "jfcby" wrote in message ... Don, Thank you for your help! How do you specify a header row in the Advance Filter. Because Row One is the header row. When I run the macro from A2 in E2 Bldg 1 is listed twice. How can the macro be modified so that the Advance Filter can begin from A2 and not include Bldg 1 twice? <BEGIN MACRO CODE Sub MakeUniqueListAndCount_1() 'Make Unique List & Count lr = Cells(Rows.Count, "A").End(xlUp).Row Range("A2:A" & lr).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("E2"), Unique:=True 'create formulas lr = Cells(Rows.Count, "E").End(xlUp).Row For i = 1 To lr Cells(i, "F").FormulaR1C1 = "=COUNTIF(C[-5],RC[-1])" Next i 'move to new sheet Cells(1, "E").Resize(lr, 2).Cut Sheets.Add ActiveSheet.Paste End Sub <END MACRO CODE Thank for your help, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I use Auto Filter the drop down list is blank | Excel Discussion (Misc queries) | |||
Drop Down list to run Auto filter on another sheet | Excel Discussion (Misc queries) | |||
Auto-filter. Cursor on first row in new list | Excel Programming | |||
Auto list/filter | Excel Discussion (Misc queries) | |||
Auto Filter From List | Excel Programming |