Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 29th 09, 04:31 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 5
Default 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   Report Post  
Old June 29th 09, 04:58 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 2,389
Default 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   Report Post  
Old June 29th 09, 05:20 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 5
Default 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   Report Post  
Old June 29th 09, 06:21 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,510
Default 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   Report Post  
Old June 29th 09, 08:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 793
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
cannot use autofill after filtering alibaba Excel Discussion (Misc queries) 1 June 29th 09 06:33 AM
filtering/autofill problem [email protected] Excel Worksheet Functions 0 November 7th 06 01:15 PM
Autofill until uberathlete Excel Discussion (Misc queries) 1 November 9th 05 07:01 PM
trouble filtering a list. Why isn't column filtering? Pat Excel Worksheet Functions 1 July 18th 05 03:30 PM
Autofill: Need to autofill one week block, (5) weekday only into cells. dstock Excel Discussion (Misc queries) 1 June 17th 05 08:21 PM


All times are GMT +1. The time now is 09:28 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017