VBA - delete empty columns containing headers in query table-CodePudding

I have a table as a result of PowerQuery (call it Table 2) on the same sheet as the other table where is applied VBA macro (call it Table 1). Unfortunately, when macro finishes, every time the range of Table 2 expands for a few blank columns with headers.

How can I handle it in VBA - add the line code to my current at the end to delete the added columns?

Table structure breaks after run macro of first table:

My suggested end-part of the code is

Range("NP_Data_output[[#All],[Column58]:[Column57]]").Select
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete
Selection.ListObject.ListColumns(6).Delete

CodePudding user response：

Assume the columns to keep are Column1 to Column8

Sub Macro1()
    Dim c As ListColumn
    For Each c In ActiveSheet.ListObjects("NP_Data_output").ListColumns
        If Not c.Name Like "Column[1-8]" Then
            c.Delete
        End If
     Next
End Sub