Results 1 to 9 of 9

Thread: vba correct syntax needed
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba correct syntax needed

    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,316
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: vba correct syntax needed

    Try this

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

  3. #3
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba correct syntax needed

    Quote Originally Posted by DanteAmor View Post
    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

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,316
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: vba correct syntax needed

    Quote Originally Posted by parkerbelt View Post
    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
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba correct syntax needed

    Quote Originally Posted by DanteAmor View Post
    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!!

  6. #6
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba correct syntax needed

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

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,316
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: vba correct syntax needed

    Quote Originally Posted by parkerbelt View Post
    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.
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba correct syntax needed

    Quote Originally Posted by DanteAmor View Post
    What a pity, the 2 work for me.
    Maybe the excel version. I have 2007.
    Could be. I have 365 ProPlus version

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,316
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: vba correct syntax needed

    Quote Originally Posted by parkerbelt View Post
    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •