vba correct syntax needed

[parkerbelt]

I have a program and I have some code that deletes columns from a table starting at week 2 and going to the right to the last column. I recorded the syntax from the Excel VBA recorder and it has quite a few lines. I want to condense it, so there aren't as many lines, as well as, make it more dynamic, because the number of columns will change in the future. Right now, starting at week 2 and going to the right, there are 18 columns, but in the future, there will be 50 or 52, etc...

Does anyone know how to make this code condensed and dynamic, so I can delete table columns?

Sheets("Test Stores - Chart Data").Select    Range("Table1[[#Headers],[Week 2]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete
    Selection.ListObject.ListColumns(3).Delete

 

[DanteAmor]

Try this

Sub Test()
Sheets("Test Stores - Chart Data").ListObjects("Table1").Range.Offset(, 2).EntireColumn.Delete
End Sub

 

[parkerbelt]

Try this

Sub Test()
Sheets("Test Stores - Chart Data").ListObjects("Table1").Range.Offset(, 2).EntireColumn.Delete
End Sub

That code brought back "Run-time error '1004':

Delete method of Range class failed

 

[DanteAmor]

That code brought back "Run-time error '1004':

Delete method of Range class failed

Works for me

Try this:

Sub test2()
    Dim sh As Worksheet, lst As ListObject
    Set sh = Sheets("Test Stores - Chart Data")
    Set lst = sh.ListObjects("Table1")
    lst.Range.Offset(, 2).Resize(1, lst.ListColumns.Count - 2).EntireColumn.Delete
End Sub

 

[parkerbelt]

Works for me

Try this:

Sub test2()
    Dim sh As Worksheet, lst As ListObject
    Set sh = Sheets("Test Stores - Chart Data")
    Set lst = sh.ListObjects("Table1")
    lst.Range.Offset(, 2).Resize(1, lst.ListColumns.Count - 2).EntireColumn.Delete
End Sub

I ended up doing this and it worked:

Sheets("Test Stores - Chart Data").Select    
    lastTableCol = Cells(3, Columns.Count).End(xlToLeft).Column
    
    Range("Table1[[#Headers],[Week 2]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
        
       
    Dim tbl As ListObject
    Dim sourceSheet As Worksheet
    Dim i As Integer
    
    'Set the table from which column is to be deleted
    Set tbl = ActiveSheet.ListObjects("Table1")
    
    'Set the sheet that contains the table
    Set sourceSheet = Sheets("Test Stores - Chart Data")
'    Set sourceSheet = Sheet2
    
    'Run the loop twice as we need to delete 2 columns
    For i = 1 To lastTableCol - 2
'    For i = 1 To 2
        With Source
            tbl.ListColumns(3).Delete
'            tbl.ListColumns(5).Delete
        End With
    Next i

I'll try your code real quick and let you know. Thanks for your help!!

 

[parkerbelt]

I got the same error using your new code. Thanks though!!

 

[DanteAmor]

I got the same error using your new code. Thanks though!!

What a pity, the 2 work for me.
Maybe the excel version. I have 2007.

 

[parkerbelt]

What a pity, the 2 work for me.
Maybe the excel version. I have 2007.

Could be. I have 365 ProPlus version

 

[DanteAmor]

Could be. I have 365 ProPlus version

I have no way to test in 365, maybe someone will see the thread and help us with a code for 365.