Clear Contents - Data Next to Query Auto Formatting Override

[bs0d]

I have a query in Column A. I'm adding and removing data in the columns to the right.

My problem occurs when I enter, and subsequently clearcontents in the same header row as the query in column A, excel automatically adds "column1". This would continue to "column2" and "column3" if I had data in columns B, C and so on.

The only way to get it to disappear is to delete the entire column(s). I cannot do this because it would break other references I have.

Is there a setting I need to disable or enable that prevents this auto formatting in Excel for my query?

 

[bs0d]

I think Excel is trying to outsmart the user and is expanding the named range of the query. Although I haven't figured out how to prevent it from doing this. if the values are put to the cells in VBA, it seems to bypass the auto formatting. But if you manually key in a value, it picks it up (auto filter applied).

 

[bs0d]

[SOLVED]

Converting the table (query) to a range did the trick.

With Worksheets("Sheet_Name_Here").ListObjects("Query_Name")
     .Unlist
End With