VBA How to Add Blank Rows in a Table

Question

I have been messing with this problem for some hours now and obviously I am missing something important. I'm trying to add some blank rows in an Excel Table based on criteria ID.

This my Code, but some somehow it ends up on an infinite loop right after ID 102. I know you have to delete rows from the bottom up but not sure for adding rows.

Sub AddBlnkRow()

Dim Tbl As ListObject, TblID As Range, c As Range

Set Tbl = ActiveSheet.ListObjects("TblState")
Set TblID = Tbl.ListColumns(1).DataBodyRange


For Each c In TblID        'Step -1

   If c = 200 Or c = 300 Or c = 400 Then Tbl.ListRows.Add (c.Row - 2)

Next


End Sub

Any help is appreciated

Nick.

Out of curiosity, why are you doing this? One should strive not to add blank lines in data. If you want a visual separation, there'are alternatives such as formatting and even increasing the row height, etc — Super Symmetry
– Super Symmetry, Commented Aug 19, 2020 at 7:44
This is just a made up example of what I have. The Table comes from PQ and my old boss can't see very well so he asked me to somehow separate it. I said no problem and then turns out it was more of a challenge than I thought. — I Forte Si Daja
– I Forte Si Daja, Commented Aug 19, 2020 at 8:40

Super Symmetry · Accepted Answer · 2020-08-19 08:44:14Z

2

You should also loop backwards if you're adding rows in the middle of a range.

Sub AddBlnkRow()
    Dim Tbl As ListObject
    Dim i As Long
    
    Set Tbl = ActiveSheet.ListObjects("TblState")
    
    With Tbl.ListColumns(1).DataBodyRange
        
        '* Count down to 2 as you don't want a blank line
        '* in the first row
        For i = .Rows.Count To 2 Step -1
            
            '* Use Mod to check if value is multiple of 100
            If .Cells(i, 1) Mod 100 = 0 And Len(.Cells(i, 1)) > 0 Then
                Tbl.ListRows.Add i
            End If
        Next i
    End With
End Sub

edited Aug 19, 2020 at 8:44

answered Aug 19, 2020 at 7:41

Super Symmetry

2,8851 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tomasz Paluch · Accepted Answer · 2020-08-19 07:17:29Z

1

A quick workaround (added a skipRow to bypass cells which already have an empty row added before):

Sub AddBlnkRow()

    Dim Tbl As ListObject, TblID As Range, c As Range
    Dim skipRow As Boolean

    Set Tbl = ActiveSheet.ListObjects("TblState")
    Set TblID = Tbl.ListColumns(1).DataBodyRange
        
    For Each c In TblID
    
            If (c = 200 Or c = 300 Or c = 400) And skipRow = False Then
                Tbl.ListRows.Add (c.Row - 2)
                skipRow = True
            Else
                skipRow = False
            End If
    
    Next

End Sub

answered Aug 19, 2020 at 7:17

Tomasz Paluch

3551 gold badge7 silver badges12 bronze badges

3 Comments

I Forte Si Daja Over a year ago

Thanks buddy, That is excactly what I was looking, could you please explain briefly how this skipRow works, I have never used it before.

Tomasz Paluch Over a year ago

This is simply a Boolean variable (acting as a flag here). It is evaluated during every iteration of the for each loop (added to the condition deciding whether to add a row or not). If a row has already been added above the current cell, it makes it skip that cell and also reset its value to False. Just try debugging it and you will get to know it very easily.

I Forte Si Daja Over a year ago

Hi Tomasz, It took me a while to figure out, I went through every line with F8 and I think I see what is going on here. So it loops through the list and when it finds ID 200 it adds a row, the next loop it finds ID 200 again, but this time it does nothing since the SkipRow value has changed. I thought when the loop starts and finds ID 200 the next time it loops finds ID 201 but that wasn't the case, somehow when you add a row it kind of sets the loop one step back. I guess that is why I ended up with an infinite rows between 102 and 200. Thanks man, I learned something new today.

Collectives™ on Stack Overflow

VBA How to Add Blank Rows in a Table

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related