VBA - How to add empty rows until Row 10-20-30-... after unique values in column A

[Jerbu]

Hello Mr.Excel community. For a collaboration at work we are looking at providing labels to our partner in Africa, however, printing those labels based on the excel file provided is proving to be a challenge to my limited VBA knowledge and I was hoping somebody could help me out.

I have a rather large excell file containing in column A a (unique) household identifier and in column B a household member ID. Depending on the number of members in a household, some column A values will happen 1-2-3-... times. For label printing purposes, I would need a label page per household, which comes down to 10 lines. So I need to add a variable number of empty rows to the data, if household ID1 occurs 2 times, I want 8 empty rows added behind it, if household ID2 occurs 7 times, 3 empty rows are to be added etc... all the way down.

I've been breaking my head over this (and learning stuff in the proces), but I just can't seem to get there. Any help would be much appreciated.

 

[JLGWhiz]

give this a try.

Sub t()
Dim i As Long, cnt As Long
With ActiveSheet
    For i = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row To 2 Step -1
        cnt = Application.CountIf(.Range("A:A"), .Cells(i, 1).Value)
        Rows(i + 1).Resize(10 - cnt).Insert
        i = i + 1 - cnt
    Next
End With
End Sub

There is a possibility that it will not work while you data is in table configuration. But you can still try it.

 

[Jerbu]

Thanks a lot for this! There were some errors at first due to some strange data (>10 houshold members) in my tables, but this script put me on the right path to figure the rest out myself and I have the layout I need now.

 

[JLGWhiz]

Thanks for the feedback,
Regards, JLG