Column disappear when using advanced Filtering through VBA

[GIS2013]

Hi, I'm struggling to understand why after the first loop (Z=2) all of a sudden column A in WS ATT disappears. Worth adding that column M in WS L is a "dynamic" range (at first it contains 7 cells) in the sense that it shrinks with each loop (M2:M8, second loop: M2:M7 etc.). I can produce a sample, but maybe somebody see why my prob. is happening through this code?

Please Login or Register  to view this content.

[GIS2013]

It gets even more weird. The porblem is "solved" only if I select the second worksheet (one of the two that exists before running the loop above). I cannot see the logic here..

If it may help here is the full code

Please Login or Register  to view this content.

[Norie]

Have you checked what's actually selected before you delete the Selection?

PS Bad idea calling a sub Delete.

[GIS2013]

Hi Norie,
Yeah I'll change the name.
As to the selection: it's a loop, for example Z=2 TO 5, so in each loop the M column is shrinking.
This was my solution (I guess that are better) to the fact that in advanced filtering you must provide contagious range..

Clarification: in the criteria range "A1:N2" I always filter by a specific field ("M1") and each time M2 is changing. And each soltion is copied into a new sheet which is added and named after the M2.value

[Norie]

But have you checked what's actually selected before you delete it?

One of the pitfalls of using Select/Selection is that you can never actually be sure what's selected.

The right sheet might not even be selected.

If you use explicit workbook/worksheet/range references you shouldn't have that problem.

[GIS2013]

Thanks Norie . Let me revise my whole code and meanwhile mark this thread as solved.

[Norie]

Is it solved?

Have you rectified the problem with the disappearing column?

[GIS2013]

I'll check your direction tomorrow (and update). For now the "patch" is just to activate L (which is a worksheet) just before running the loop. Some how it works this way without "deleting" the first column.

[GIS2013]

Norie, you were right, explicit reference has made the difference. I've changed:
this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

And now no column is got lost..