What stops auto fill working

[Sunline]

Hello all ,
I'm trying to use auto fill in its normal way , where you ,
Highlight col
Ctrl +G
Special
Blanks
OK
Then it starts going thru a random selection looking at cells . i can see this happening in the pane directly under the word clipboard . Top left in excel .
It tells me no cells found .
I had this working but am getting this all the time .
I also had a macro written which worked but now just auto fills only row 2 cell and fills to bottom which should be finding all other cells to auto fill . Confused . what might i be doing wrong .

Thanks .

 

[Alex Blakenburg]

Good call by rollis

Sorry but XL2BB is useless in this case. Contents of real cells are need.

It looks like you have received your data from a database. See here for how you can have apparently blank cells that are not registering as blanks (particularly the heading "more information")
The result is false when you use the ISBLANK function - Office
If under File > Options > Advanced > Lotus Compatibility settings > Transition navigation keys > Turn this on, you will be able to see a single quotation mark in the cells
see: Fix Blank Excel Cells Copied From Database – Contextures Blog, specifically the section "See the Hidden Content"
You only seem to be able to isolate that character using VBA Range.PrefixCharacter and even then only if you have turned on the above setting.

You can easily convert the cells appearing to be blank to real blanks.

In your case immediately after setting the last row in this line:

vlRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Paste the values back on to itself using the following:-

    Range("F2:F" & vlRow).Value = Range("F2:F" & vlRow).Value
    Range("G2:G" & vlRow).Value = Range("G2:G" & vlRow).Value

 

[Jimmywick]

Usually, in any version of Microsoft Excel, the Fill Handle option remains activated by default. But sometimes there could occur some issues that may disable the Fill Handle option. In that case, we have to enable the Fill Handle option manually.

What you have to do is go to the Advanced tab in the Excel Options menu first. Then Under the Editing Options bar, mark on the options showing the statements ‘Enable fill handle and cell drag-and-drop’ and ‘Enable AutoComplete for cell values’.

 

[Sunline]

Thanks guys , I made these changes and and can mark as solution .

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.