VBA - override autofilter warning message

[no_Fate]

Good evening guys,

Just a quick (I hope) question:
I'm creating a tool to use at work. It will essentially perform an autofilter operation.
The thing is that I want the results to be only 5, so that's the limit I have in my destination range and, if by any inexplicable reason, someone search for half the number they're supposed to, the number of results will be high... if this happens the message "The destination range is not large enough to accommodate all copied rows. Data below the destination range will be lost. Continue copying anyway?" is displayed.

All this to ask: Is it possible to override this message? (I need other to be displayed if other conditions are meet, so I cannot use the "DisplayAlerts = False" code. Or is there any way for the code always answer "No" to this question?

Thank you.

 

[Jerry Sullivan]

Hi no_Fate,

I'd suggest you write the VBA code so that it checks the number of filtered rows against the maximum number of destination rows. That allows you to control any user warning message and next action to be taken.

If you are using the AdvancedFilter function, then I'd suggest writing the filter results to a temporary sheet. That will allow you to preview the results before attempting to copy them to the destination.