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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try this

Code:
Sub Test()
Sheets("Test Stores - Chart Data").ListObjects("Table1").Range.Offset(, 2).EntireColumn.Delete
End Sub
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top