VBA to disable automatic formula UPDATING not Calculation

So here's the problem.
I have a template in which I have 2 cells with vlookup formulas that point to a fixed range (with the $) on a help sheet.
Now the macro pulls in data from another sheet on to this help sheet onto the fixed range that is used in the formula.

However the data which is copied has empty columns between the filles columsn.
With a piece of vba code I take this empty columns out and the lookup range is back to its original state again so no worries I thought.

However the vlookup formula reacts on this deleting of empty columns by adjusting my (fixed) lookup range.
So ie at start the lookup range is $G:$T and after is $G:$N and antother run of the macro brings it to $G:$H.

Normally I welcome this excel inteligence of adjusting your formulas when you delete rows, columns or move things.
But this time it is certainly not welcome.

I tried with :

Please Login or Register  to view this content.

And then the deletion of the empty columns between turning off and on the calculation.

But this wont do the trick....

So is there also code to disable the automatic updating of formulas?
And as above I am not refering to automatic calculation of the formulas because that is something different.
Thanks.

If the formula is in the VBA you can re apply it to the same range after the columns have been deleted. That's the first thought that came into my head. Worth a try

Thanks Phil you are right in that. But that was my last resort
It is not a simple vlookup, the lookup is part of a much much more complex formula.
Could record it and that way put it in the code.
But I would like to have the formula in the top cell just to be able to copy it manually down if I have to ammend the list manually.

But you are right when there is no VBA solution that is probably what I will need to do but hopefully somebody knows a VBA solution.

thanks sixthsense so what you are saying is that it just cannot be done because it always will adjust automatically?

Ok I solved my problem with a little detour but at least I have now what I wanted

So I did not paste the new data directly to the lookup range but I copied to columns AA and further.
Did the removing of empty columns there and than copied and paste the correct new lookup area to column G and further.

This way the lookup formulas are not influenced and I got exactly what I wanted with this slight detour
Thanks all for your inputs.