How can I loop ".Resize Range()" to dynamically adjust a table range?

[oddzac]

I'm working with a dynamic table that populates names based on the given weeks data. I have a macro that refreshes and sorts the data as needed but am currently working with the .Resize Range() function to adjust the table based on how the rows populate.
Note: "Login" cells populate based on an IF reference to another sheet:

=IF(WSSW!AH126=0,"-",WSSW!AH126)

See table ("Table12") itself below (webdings for privacy)

Prior to including the loop,

mnTbl.Resize Range("$A$3:" & lcol & lrow - 1)

(to remove one row)

and

mnTbl.Resize Range("$A$3:" & lcol & lrow + 1)

(to add one row)

did exactly what I needed them to.

The trouble started when I wanted the macro to decide which operation to run based on the the last cell in my "Login" column.
The currently included Do Until loop freezes my application but I left it for visibility. The real goal though is to have the macro decide if it should add a row or remove one.

The Code:

Sub TableDrag()
'
' TableDrag Macro
'

'
Dim MAIN As Worksheet
Set MAIN = ThisWorkbook.Sheets("MAIN")

Dim mnTbl As ListObject
Set mnTbl = MAIN.ListObjects("Table12")

Dim lrow As String
Dim val As String


' Find Last Row
fcol = "A"
lrow = mnTbl.Range.Rows(mnTbl.Range.Rows.Count).Row
val = Range("A" & lrow).Value

'This next part crashes excel. The goal being: IF val equals "-" THEN remove last row AND IF val not equal "-" THEN add row to end of range

Do Until val <> "-"
    mnTbl.Resize Range("$A$3:" & lcol & lrow - 1)
    Loop



End Sub

 

[oddzac]

Missed my edit window, but I accidentally removed a needed variable declaration (lcol = "J")

 

[DanteAmor]

Hi @oddzac

I have not tried it, but you have the variable fcol = "A" but in the macro you use lcol.

Maybe it should be fcol.

fcol = "A"
Do Until val <> "-"
    mnTbl.Resize Range("$A$3:" & lcol & lrow - 1)
    Loop

Missed my edit window, but I accidentally removed a needed variable declaration (lcol = "J")

To avoid these problems, write the Option Explicit statement at the beginning of each code:

When you try to run the macro, it will validate that all variables are declared. That helps.

----- --
I hope the advice helps you for your future macros.
Cordially
Dante Amor
------ --

 

[Alex Blakenburg]

We probably wouldn't do it the way you are doing it. You wouldn't really want a column with formulas that are partly inside and partly outside the table. Since you are using macros anyway you would cater for that in the macro.

In your current macro
• You are not changing the value and lrow inside the Loop so the loop is just repeating without changing anything so if val = "-" and it goes into the loop it never changes value to exit the loop.
• Values for rows should be Dim as numeric preferably Long
• Your initial post talks about increasing and decreasing the table size, the loop is only looking at decreasing.

Working with your current approach see if the below helps.
I opted not to resize the table on every iteration but do it once at the end.

Sub TableDrag()
'
' TableDrag Macro
'
Dim MAIN As Worksheet
Set MAIN = ThisWorkbook.Sheets("MAIN")

Dim mnTbl As ListObject
Set mnTbl = MAIN.ListObjects("Table12")

Dim lrow As Long
Dim val As String
Dim lcol As Long
Dim frow As Long, fcol As Long

' Find First & Last Row & Col
frow = mnTbl.Range.Row
fcol = mnTbl.Range.Column
lrow = mnTbl.Range.Rows(mnTbl.Range.Rows.Count).Row
lcol = mnTbl.Range.Columns(mnTbl.Range.Columns.Count).Column

' Value in first column of last row in table
val = Cells(lrow, fcol).Value

Dim increment_by As Long
If val = "-" Then
    increment_by = -1
    Do Until val <> "-" Or lrow = frow + 1
        lrow = lrow + increment_by
        val = Cells(lrow, fcol).Value
    Loop

Else
    increment_by = 1
    Do Until val = "-" Or val = ""
        lrow = lrow + increment_by
        val = Cells(lrow, fcol).Value
    Loop
    lrow = lrow - 1

End If

mnTbl.Resize Range(Cells(frow, fcol), Cells(lrow, lcol))

End Sub

 

[oddzac]

We probably wouldn't do it the way you are doing it. You wouldn't really want a column with formulas that are partly inside and partly outside the table. Since you are using macros anyway you would cater for that in the macro.

In your current macro
• You are not changing the value and lrow inside the Loop so the loop is just repeating without changing anything so if val = "-" and it goes into the loop it never changes value to exit the loop.
• Values for rows should be Dim as numeric preferably Long
• Your initial post talks about increasing and decreasing the table size, the loop is only looking at decreasing.

Working with your current approach see if the below helps.
I opted not to resize the table on every iteration but do it once at the end.

You hit the nail exactly on the head. Had someone in SO call out that I wasn't changing anything within the loop. I only listed the decrease because that's where I'd gotten before breaking the routine with the loop. I'm still pretty fresh with a lot of this (just a couple of months under my belt since I first started fiddling) and don't know much about formatting and syntax, but I definitely appreciate the feedback.

This is what I landed on, essentially doing exactly what you said and having it define the value once instead of looping through each row.
I've also tacked on a second loop that expands the table with flipped operators until it hits a "-" and backs up one:

Sub TableDrag()
'
' TableDrag Macro
'

'
Dim MAIN As Worksheet
Set MAIN = ThisWorkbook.Sheets("MAIN")

Dim mnTbl As ListObject
Set mnTbl = MAIN.ListObjects("Table12")

Dim lrow As String
Dim val As String
Dim rng As String


' Find Last Row

lcol = "J"
lrow = mnTbl.Range.Rows(mnTbl.Range.Rows.Count).Row
val = Range("A" & lrow).Value


' Loop through last cell values to resize table

Do Until Range("A" & lrow).Value <> "-"
    lrow = lrow - 1
Loop

mnTbl.Resize Range("$A$3:" & lcol & lrow)


Do Until Range("A" & lrow).Value = "-"
    lrow = lrow + 1
    mnTbl.Resize Range("$A$3:" & lcol & lrow)
Loop

mnTbl.Resize Range("$A$3:" & lcol & lrow - 1)



End Sub

 

[oddzac]

Just tacked on a "cleanup" step on the other side of those to remove dead formulas from outside the table

'Cleanup

lrow = lrow - 1

'Clear cells below table

Range("A" & lrow + 1, lcol & lrow + 50).ClearContents

c

Edit: Realizing now that the lrow-1 declaration and the lrow+1 in the range cancel each other out..

 

[oddzac]

To avoid these problems, write the Option Explicit statement at the beginning of each code:
View attachment 91073
When you try to run the macro, it will validate that all variables are declared. That helps.

----- --
I hope the advice helps you for your future macros.
Cordially
Dante Amor
------ --

Thanks for the tip! The script that I posted was more like a sketch than a finished product. I left some things that I had declared and decided not to use without thinking.
Should have taken the time to re-read it before posting so that's on me. Still option explicit seems really useful for cleaning up things like that, so thanks!

 

[Alex Blakenburg]

Thanks for showing what you ended up with. Glad we could help.