VBA. Rezise table with dynamic last column

Question

I am struggling with what should be a simple thing to do. How to resize the table which has a dynamic last column?

Sub rezise_table()
Dim lcol As Long
lcol = ActiveSheet.ListObjects("tblN").DataBodyRange.Columns.Count
        ActiveSheet.ListObjects("tblN").Resize Range("A1" & lcol)
End Sub

This gives a Run-time error 1004 - "wrong align error". I understand I that the code does not know the last row, but how to state it when the last column is dynamic? Searching gave me no similar cases, which is wierd, because it should be a rather common case? Perhaps i search with wrong keywords... Help appreciated.

I posted only a relevant part of the code. What I actually want to accomplish is clear the table databodyrange and rezise it to have only 2 rows in databodyrange. So the table will have a total 3 rows with variable column count depending how many columns there are at the time of risizing. To give you an example. This code must work on both cases: Table is A1:C3 or Table is A1:D3 — Kristjan Minn, Mar 11 '19 at 08:41

score 2 · Accepted Answer · answered Mar 11 '19 at 08:42

2

DataBodyRange.Columns will already give you the columns in the table. So why resize it to that? I think you want to find the actual column in the worksheet and not the table?

Also Range("A1" & lcol) in your actual code is incorrect. If your last column is 10 then "A1" & lcol becomes A110. See the code below.

Is this what you are trying? I am assuming that row 1 of the activesheet has the headers.

Sub rezise_table()
    Dim lcol As Long, lRow As Long
    Dim lastCol As String
    Dim ws As Worksheet

    Set ws = ActiveSheet

    With ws
        lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lRow = 3 '<~~ 2 rows + 1 Header row

        lastCol = Split(.Cells(, lcol).Address, "$")(1)

        .ListObjects("tblN").Resize .Range("A1:" & lastCol & lRow)
    End With
End Sub

answered Mar 11 '19 at 08:42

Siddharth Rout

147,039
17
206
250

Thanks! You helped me a lot! – Kristjan Minn Mar 11 '19 at 09:00
@Siddharth Rout In this line `lastCol = Split(.Cells(, lcol).Address, "$")(1)` Kindly elaborate last bracketed part when `(0)` should be used vs `(1).` in the context of this question related to table range. Not quite clear to me and may not justify a separate question for this minor point Or may please provide pointer which I shall go through. – skkakkar Mar 11 '19 at 10:46
1

@skkakkar: The cells address that you will get will be something like `$A$1` or `$AA$1` or something similar in format. The code will split the address on "$" and we need the column name so It will always be `1` :) This code is also mentione [HERE](https://stackoverflow.com/questions/10106465/excel-column-number-from-column-name/10107264#10107264) – Siddharth Rout Mar 11 '19 at 10:54
1

@Siddharth Rout Thanks a lot Your helpful attitude is very much.appreciated. – skkakkar Mar 11 '19 at 10:57

score 0 · Answer 2 · answered Mar 11 '19 at 09:04

My final working code for reference (takes the table name at A1. Table itself starts at row 3, ends at row 5):

Sub clearandresize()
Dim tblN As String, lcol, lrow As Long, ws As Worksheet
Set ws = ActiveSheet
tblN = ws.Range("A1")
ActiveSheet.ListObjects(tblN).AutoFilter.ShowAllData
Range(tblN).Select
    Selection.ClearContents
    With ws
        'lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lcol = ws.ListObjects(tblN).DataBodyRange.Columns.Count
        lrow = 5 '<~~ 2 rows + 1 Header row
        lastCol = Split(.Cells(, lcol).Address, "$")(1)
        .ListObjects(tblN).Resize .Range("A3:" & lastCol & lrow)
    End With
    Range("A1").Select
Application.CutCopyMode = False
End Sub

VBA. Rezise table with dynamic last column

2 Answers2