How to write "Do Until" loop that resizes table?

Question

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) and mnTbl.Resize Range("$A$3:" & lcol & lrow + 1) 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 current state visibility. The goal is to have the macro decide if it should add a row or remove 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


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

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



End Sub

Should I even be using a Do Until for this? I'm not very familiar with loops in general, but it seems like I should be able to loop an IF/THEN/ELSE to accomplish the same thing.. — oddzac, May 05 '23 at 21:58

score 1 · Accepted Answer · answered May 05 '23 at 22:00

Before entering the loop, val = "-". The loop repeats until val <> "-". But the value of val is never being changed in the loop. val will always be "-", so the loop will never end.

Similar problem with lrow - 1. The value of lrow is never being changed within the loop. If lrow = 131 before entering your loop, then every loop will just be lrow - 1 = 130.

Whatever condition the loop is testing, the result of that condition should be changed within the loop. In your case, the value of val should be changed within the loop. Without modifying your code too much, try:

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

To clean things up a little, it would be slightly neater to just test the value of Range("A" & lrow).Value, and not resize your range until after the loop:

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

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

This works flawlessly to cut the table down if it's oversized. I flipped the operators in a following loop: `Do Until Range("A" & lrow).Value = "-" lrow = lrow + 1 Loop mnTbl.Resize Range("$A$3:" & lcol & lrow - 1)` and it worked like a charm. apologies for bad formatting — oddzac, May 05 '23 at 22:22

oddzac · Answer 2 · 2023-05-20T14:53:24.120

Final Formatting for posterity:

    Dim MAIN As Worksheet
    Dim mnTbl As ListObject
    Dim lrow As String
    Dim val As String
    Set MAIN = ThisWorkbook.Sheets("MAIN")
    Set mnTbl = MAIN.ListObjects("Table12")


'========Resize Table




' 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

'First operation finds first row in the tables current state 
'containing null value "-" in login column 
'and resizes table to that row minus one

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

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


'Second operation extends the table range
'by lrow plus one until the range value
'equals null value of "-"

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

'After loop breaks, resize table by
'removing lrow left over from second operation

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

'Cleanup

'Clear cells below table

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

Had to put the Resize operation inside the loop for adding rows with another outside the loop that backs it up one row after the loop breaks.

Also added a "cleanup" routine since the main chunk leaves a mess of formulas and formatted cells behind after the resize is complete.

Thanks @kevin for the concise answer and explanation. Really appreciate having the "why" alongside the answer!

How to write "Do Until" loop that resizes table?

2 Answers2