vba correct syntax needed

parkerbelt

Active Member
Joined
May 23, 2014
Messages
377
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?

Code:
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,169
Office Version
2007
Platform
Windows
Try this

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

parkerbelt

Active Member
Joined
May 23, 2014
Messages
377
Try this

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,169
Office Version
2007
Platform
Windows
That code brought back "Run-time error '1004':

Delete method of Range class failed
Works for me

Try this:

Code:
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

Active Member
Joined
May 23, 2014
Messages
377
Works for me

Try this:

Code:
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:

Code:
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!!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,169
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,048
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top