VBA delete table column based on selection

[Doug Mutzig]

Hi All!

I am very new at VBA and need help with an issue I have.

I have a table on a worksheet that I will like the end user to be able to select a cell and then click a button to remove the entire table column. I have tried searching but nothing I have found relates to columns in a table. I did find a wonderful post on removing rows in a table: https://www.mrexcel.com/forum/excel-questions/683740-vba-delete-table-row-based-selection-2.html

I would love to be able to modify this to work for columns instead, but I am at a loss on where to begin, or if there is a better way.

Additionally, I have a button to add a column to a table but I also need the 2 cells above the table to also be added above the new column, is this possible?

Thank you all for any help and guidance you can provide on this!
Doug

 

[Doug Mutzig]

I really like the ability to clear the cells above, but is it possible to delete them? There is only 1 table on the work sheet with nothing below it as it will expand depending on the amount of data entered. Zack you mentioned a ListObject is this for the selection of the cells above the table?

I can reformat the worksheet so that nothing is above or below the table if simply removing the entire column is easier. I just want to limit what the end user does so that things are less likely to be broken which means I need the cells above to stay matched with the below table column.

I really appreciate all the help and advice you both have given!
Doug

 

[gallen]

My bad I should have used delete, not clear.

This code does as you say. However, as Zack rightly states, the code relies on the header row being visible.

Sub DelCol()


    Dim tbl As ListObject
    Dim sColName As String
    Dim r1 As Range, r2 As Range
    
    Set tbl = Me.ListObjects("Table1")
    
    'ensure user has only selected a single cell
    If ActiveCell.Cells.Count <> 1 Then Exit Sub
    
    'check if selected cell is within the table
    If Intersect(ActiveCell, tbl.DataBodyRange) Is Nothing Then
        MsgBox "Selected cell is not within the table", vbCritical
        Exit Sub
    End If
    
    'get the column header of the table
    sColName = Cells(tbl.HeaderRowRange.Row, ActiveCell.Column)
    
    'get first cell above table
    Set r1 = Cells(tbl.HeaderRowRange.Row, tbl.ListColumns(sColName).Range.Column).Offset(-1, 0)
    'get 2nd cell above table
    Set r2 = Cells(tbl.HeaderRowRange.Row, tbl.ListColumns(sColName).Range.Column).Offset(-2, 0)
    'delete the cells, shifting to left
    Range(r1, r2).Delete shift:=xlToLeft


    'use the column header to denote which table column to delete
    tbl.ListColumns(sColName).Delete
    
End Sub

 

[Zack Barresse]

You could use something like this to use the ListObject member, based dynamically off the Table range...

    Dim TopRow As Long
    
    TopRow = WorksheetFunction.Max(Table.Range.Row - 2, 0)
    If TopRow > 0 Then
        Table.Range(1, 1).Offset(-(Table.Range.Row - TopRow), 0).Resize(Table.Range.Row - TopRow, Table.ListColumns.Count).Delete Shift:=xlUp
    End If

This won't remove any rows unless there is at least 2 empty rows.

 

[Doug Mutzig]

Hi Gallen,

The code works perfectly! Thank you very much for all your help on this!

I have another question related to the table I am working on; however it is different than what we have been discussing. Should I start a new thread or continue in this thread?

Again, thank you for your help and knowledge!
Doug

 

[Doug Mutzig]

Hi Zack,

Would this be something that is a new macro or would this be added to the code you gave earlier or to Gallens?

Thank you for the different perspective on tackling these questions. I am learning that there is often no one way to do things with VBA which is great and scary at the same time

 

[Zack Barresse]

Yeah, you could add it if you wanted. Can do pretty much whatever you want, that's the great thing about VBA as you point out.

Edit: the last code I posted made use of a variable Table, which wasn't set. That would be the Table you are working with. So if you're using the previous code, change it to that.

 

[gallen]

Hi Gallen,

The code works perfectly! Thank you very much for all your help on this!

I have another question related to the table I am working on; however it is different than what we have been discussing. Should I start a new thread or continue in this thread?

Again, thank you for your help and knowledge!
Doug

Hi Doug, Yes if it is a separate issue, start a new thread.