In need of a macro to help me organize long lists of words

Hi Shakiru,

Thank You for helping me out with this. I plugged it in, adjusted the numGroups and groupsize variables accordingly. I changed the groupsize to 5 for testing purposes. After I ran it, I noticed the following:

> Besides pushing down the word that is sitting in the designated row and then inserting 'Group X (xx-xx)' into that cell, it is actually replacing the word that is already in that row with 'Group X (xx-xx)'. Which means I’m losing every 5th word in my list.

> I see that the indication of the included words within the ranges shows up on the designated row as well as the group title. The addition of the designation isn’t required. Once the groups are all completed, I will be copying & pasting them directly to a different document, where only the title and respective number would be needed.

> When I run the macro, I’ve included more than one column within my range selection, but I see only the first column within my selection is being manipulated. Is this by design? If so, it is my fault for not making myself clear. I would like to adjust multiple columns simultaneously, if at all possible.

> After it runs, it fills in each 5th row as I mentioned above, but then the ‘Run-time error ‘91’ pops up and the following line within the code -> row.Offset(0, 0).Value = groupTitle is highlighted yellow. So since there is some debugging that is needed, I am assuming that I made a misstep within this process somewhere.

Thanks Shakiru

WB

Wayne,

Assuming the following:

• you didn't already split your data
• your data are in subsequent columns starting in A2.

then this code could do the work for you:

Sub Grouping() 

    Dim sh As Worksheet 

    Dim rng As Range 

    Dim rngT As Range 

    Dim i, k As Long 

    Dim lngGroup, lngGroups As Long, lngGap As Long 

    Dim varGroupNames As Variant 

    Application.ScreenUpdating = False 

    lngGroup = 15 'count of items in a group 

    lngGroups = 3 'count of columns 

    lngGap = 2 'count of rows to be inserted between groups 

    'fill this array with groupname you want to use, at least 

    'lngGroups names in this array 

    varGroupNames = Array("Group", "List", "Some", "Other") 

    Set sh = ActiveSheet 

    Set rng = sh.Range("A" & 2).Resize(lngGap) 

    Set rngT = rng 

    i = 0 

    Do Until rng.Resize(1) = "" 

        Set rngT = Union(rngT, rng) 

        i = i + 1 

        Set rng = sh.Range("A" & i * lngGroup + 2).Resize(lngGap) 

    Loop 

    rngT.EntireRow.Insert 

    Set rngT = rngT.Offset(-1) 

    For i = 1 To rngT.Areas.Count 

        With rngT.Areas(i).Resize(1) 

            For k = 0 To lngGroups - 1 

                .Offset(0, k) = varGroupNames(k) & " " & Format(i, "0000") 

            Next 

            With .Resize(1, lngGroups) 

                .Font.Bold = True 

                .Font.Color = RGB(80, 80, 255) 

            End With 

        End With 

    Next 

    Application.ScreenUpdating = True 

End Sub 

With the variable lngGroup you can change the count of items per group.

With the variable lngGap you can change the count of rows to be insereted between groups.

With the variable lngGroups you change the count of columns to be grouped.

And the variable varGroupNames should have the groupnames you want to use in each column.

Jan

The attachment provides a visual on how I wish the columns & groups of words to look.

Okay, but we need a sample file how your real data looks like and where it is.

Otherwise the macro can fail / did not work for you.

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Andreas.

Hi WaynePB!

Please try this: Sub TitleGroups() Dim col As Range Dim row As Range Dim i As Long Dim j As Long Dim k As Long Dim l As Long Dim numGroups As Long Dim groupSize As Long Dim groupTitle As String

' Set the number of groups and size of each group numGroups = 10 groupSize = 10

' Loop through each column For Each col In Selection.Columns i = 1 ' Start at row 1 j = 1 ' Start with group 1

' Loop through each row For Each row In col. Cells ' Check if it's time to start a new group If i Mod groupSize = 1 Then ' Build the group title groupTitle = "Group " & j If j > 1 Then groupTitle = groupTitle & " (" & (j - 1) * groupSize + 1 & "-" & i - 1 & ")" End If

' Add the group title to the row above the current row row. Offset(-1). Value = groupTitle

' Increment the group number j = j + 1 End If

' Increment the row counter i = i + 1 Next row

' Add the final group title groupTitle = "Group " & j - 1 & " (" & (j - 2) * groupSize + 1 & "-" & i - 2 & ")" row. Offset(0, 0). Value = groupTitle Next col End Sub

To use this macro, you would need to select the range of cells containing your long lists of words, and then run the macro. The macro will loop through each column in the selection and add a unique title to each group of 10 (or 15) words. The group titles will be numbered and will indicate the range of words included in each group.

Note that you may need to modify the macro to fit the exact format of your data. For example, you may need to adjust the numGroups and groupsize variables to match the number of groups and size of each group in your data.

Best Regards, Shakiru