Cannot use autofill after filtering

[alibaba]

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?

 

[Fred Smith]

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]

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.

 

[OssieMac]

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

 

[Sheeloo]

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,...