Disabling Flash Fill

[George J]

I have searched online for the solution and have not seen one, which does not bode well. Time to ask the experts.

I have a workbook that creates another workbook.
In this second workbook , the user fills in reference ID's but Excel is trying to be helpful. When it thinks it sees a pattern, Flash Fill kicks in and auto-populates the empty cells - wrongly.

Is there a VBA command that will turn off the flash fill? Currently I need to do this manually through:
File > Options > Advanced - Editing Options: disable 'automatically flash fill' while leaving 'enable autocomplete for cell values' alone.

I saw a post that mentioned adjusting the group policy, but this is not what I require.

 

[footoo]

This is what the macro recorder produced :

Application.DisplayInsertOptions = False

 

[George J]

Thanks, but this doesn't work.

I created a blank workbook and put that into the workbook open event.
Running Application.DisplayInsertOptions = False still leaves the falsh fill option ticked.
Tried running if from a macro too - it doesn't seem to turn it off.

 

[footoo]

Thanks, but this doesn't work.

I created a blank workbook and put that into the workbook open event.
Running Application.DisplayInsertOptions = False still leaves the falsh fill option ticked.
Tried running if from a macro too - it doesn't seem to turn it off.

You are right. I couldn't get it to work either.

Did a search without success except :
https://superuser.com/questions/766967/how-can-i-turn-off-flash-fill-automatically-in-excel-2013

 

[George J]

That's the group policy post I mentioned. Seems we can't do this with VBA.
Maybe I'll put a warning message on the workbook if it finds Application.FlashFillMode = TRUE

Thanks for looking at this footoo.

 

[clewis]

This is old, but I recently stumbled upon the same problem.

Application.FlashFill resolved my issues. You were 90% there with Application.FlashFillMode, so I'm sure you've discovered this by now. Just thought I'd reply for anyone in the future.