Cannot use AutoFill after Filter

[alibaba]

I need to input the seat number in column J (Commercial subject).

However, after applying the filter function on column D, i cannot use the autofill method to input the seat numbers(I need to input manually, same for other subjects).

Any method to solve this problem? Please help.

[mubashir aziz]

In D column use *cmm* instead of cmm* and your problem will be solved .....

Also, You can use Contains in custom filter , cmm if you are sure about the words which you are looking for .....

[alibaba]

I dont quite understand.
I apply the filter function on column D with the 'include' cmm option to filter.
After this filter, I cannot use the autofill handler to fill in column J.
something wrong in book2.xls, now i send book3.xls

[mubashir aziz]

But its working with me ..... one thing, are you facing this problem in this file only ?????
Just check from Tools>Edit> and Check Allow Cell Drag Drop is checked ...

in attached file in column J i just used autofill by dragging handle ...

[Richard Schollar]

Are you inputting ther exact same value in each cell? if so, select all the filtered cells you want to apply this value to, and hit Alt+; to select only the visible cells, type in the value you want to apply and then confirm with Ctrl+Enter (not just Enter).

Richard

[alibaba]

Sorry for not explaining clearly.

The point is: I cannot fill in column J in a series, that is, the seat number 001, 002, 003, 004

[Richard Schollar]

One way to get around this is to do the filter and then in the filtered cells enter the following formula (just the filtered cells, so select them with Alt+; first):

=TEXT(SUBTOTAL(3,$D$7:$D7),"000")

and confirm with Ctrl+Enter. This assumes that D7 is your first visible row - if it was actually row 123 then you would modify the formula to:

=TEXT(SUBTOTAL(3,$D$123:$D123),"000")

Then, turn Automatic Calculation off (via Tools>Options>Calculation tab and select Manual) and then remove the filter and copy and pastespecial>values over your originals (then you can turn automatic calculation back on).

Bit long-winded but if you have 50 or 60 seats you need to allocate it should speed things up.

[alibaba]

Thanks a lot. It works.

I know little about VBA. I wonder if we can automatic this process using VBA so that I can enter the series(001, 002,..) for every subject.

[mwatson23]

Thanks Richard!!

[ramimagdi88]

how can I fill odd numbers only? this function increment the value only...

[bpowers61]

OK. I'm not following at all.

I have a spreadsheet that I have applied a filter to. Now my fill handle is not working properly.

I have a column that has nothing but dates in it. I am not applying a filter on that column at all. It's "filterable", but I filtered on another column. If I start entering data in a new row with a new date and then drag down the column to increment the date accordingly, I simply just get that same date in all the cells. It's not incrementing the date like it should.

Is this a bug? Is there a work-around? Any ideas?