Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cannot use autofill after filtering
Let me make it simpler.
column A stores the exam subjects that I am going to filter. I found that after applying the data--filter--autofilter (with some creteria) on column A, a certain subject(say English) is filtered. Then I try to use autofill in column B to assign seat number to the students(001, 002, 003,..). However, I cannot fill in the series. Can anybody help me? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cannot use autofill after filtering
You're correct. Autofill does not work on filtered lists.
You could sort the list by your criterion, fill in column B, then sort it back. Regards, Fred. "alibaba" wrote in message ... Let me make it simpler. column A stores the exam subjects that I am going to filter. I found that after applying the data--filter--autofilter (with some creteria) on column A, a certain subject(say English) is filtered. Then I try to use autofill in column B to assign seat number to the students(001, 002, 003,..). However, I cannot fill in the series. Can anybody help me? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cannot use autofill after filtering
I cannot use sort in Column A because Column A contains subject abbreviations
in this format: e.g. cell A2 contains: CHI, ENG, MAT, ECO, CHS, CLI, HIS, PAC1, CMM cell A3 contains: CHI, ENG, MAT, ECO, CHS, CLI, HIS, PAC1 What I am doing now is: To arrange seat number for English, I need to use filter with the 'cell contain ENG' option to filter out English in column A and then type 001, 002, 003 manually in column B. This could be a trouble if there are more than 50 English students. "Fred Smith" wrote: You're correct. Autofill does not work on filtered lists. You could sort the list by your criterion, fill in column B, then sort it back. Regards, Fred. "alibaba" wrote in message ... Let me make it simpler. column A stores the exam subjects that I am going to filter. I found that after applying the data--filter--autofilter (with some creteria) on column A, a certain subject(say English) is filtered. Then I try to use autofill in column B to assign seat number to the students(001, 002, 003,..). However, I cannot fill in the series. Can anybody help me? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cannot use autofill after filtering
Are you interested in using macro code to autofill the required column? If so
then the following should do what you want. If using the code then first make a backup of your workbook in case it does not return the expected results. To install the macro, right click the worksheet tab name. Select view code. Copy the code into the VBA editor. Click the red X top right of VBA editor to close the editor. Save the workbook. To run the code, set the required Autofilter and then double click the column header of Column B. (If not column B where you want the autofill then you will need to edit the macro. See the green comment. Right click worksheet tab name and then View code to re-open the VBA editor at the correct place.) You will also have to set the macro security to allow macros to run and if you are using xl2007 then you will need to save as an Excel Enabled workbook. See help under macro security for more info on this. The code will pop up a message if AutoFilter is not set or if actual filter is not set and then terminate the processing. Feel free to get back to me with any questions. Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) 'Edit "$B$1" in the following line to match your 'column header cell reference. If Target.Address = "$B$1" Then Dim rngToAutoFill As Range Dim lngColumn As Long Dim lngAutoFill As Long Dim c As Range Cancel = True 'Test if AutoFilter turned on. With ActiveSheet If .AutoFilterMode = False Then MsgBox "AutoFilter is not turned on." _ & vbCrLf & "Set AutoFilter and re-run the code." Exit Sub End If 'Test if any filters are set. If .FilterMode = False Then MsgBox "No filters have actually been set." & vbCrLf & _ "Set filters and re-run the code." Exit Sub End If End With lngColumn = Target.Column With ActiveSheet.AutoFilter.Range Set rngToAutoFill = Columns(lngColumn) _ .Resize(.Rows.Count - 1, 1) _ .Offset(1, 0).SpecialCells(xlVisible) End With rngToAutoFill.Select rngToAutoFill.NumberFormat = "000" lngAutoFill = 1 For Each c In rngToAutoFill c = lngAutoFill lngAutoFill = lngAutoFill + 1 Next c End If End Sub -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cannot use autofill after filtering
My reply to your other post;
Autofill won't work with Filter. Assuming you want to assign seatnumbers whre Col A has English... (with first row having headers) enter this formula in row 2 of any col and copy down =IF(A2="English",COUNTIF($A$2:A2,"English"),"") This will enter 1,2, ... to those rows having English. You can format them as 000 to get 001,002,... "alibaba" wrote: I cannot use sort in Column A because Column A contains subject abbreviations in this format: e.g. cell A2 contains: CHI, ENG, MAT, ECO, CHS, CLI, HIS, PAC1, CMM cell A3 contains: CHI, ENG, MAT, ECO, CHS, CLI, HIS, PAC1 What I am doing now is: To arrange seat number for English, I need to use filter with the 'cell contain ENG' option to filter out English in column A and then type 001, 002, 003 manually in column B. This could be a trouble if there are more than 50 English students. "Fred Smith" wrote: You're correct. Autofill does not work on filtered lists. You could sort the list by your criterion, fill in column B, then sort it back. Regards, Fred. "alibaba" wrote in message ... Let me make it simpler. column A stores the exam subjects that I am going to filter. I found that after applying the data--filter--autofilter (with some creteria) on column A, a certain subject(say English) is filtered. Then I try to use autofill in column B to assign seat number to the students(001, 002, 003,..). However, I cannot fill in the series. Can anybody help me? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot use autofill after filtering | Excel Discussion (Misc queries) | |||
filtering/autofill problem | Excel Worksheet Functions | |||
Autofill until | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) |