Group Every Nth Row

Re: Increment By 1 The Variable in Every 503 Rows

As a macro-free solution, in column B, you can put =MOD(ROW(),503)=0 and drag that down. Apply filters to the sheet and filter to TRUE. Highlight all 1000 rows, hit Ctrl+; to select only visible cells, then Alt, I, R to insert rows. Then delete column B.

Re: Increment By 1 The Variable in Every 503 Rows

1. Yes, it is normal. It will return either True or False. You would drag this down through all 503,000 rows.
2. On the Home tab, toward the right is a sort and filter button. Hit Ctrl+A, then this button, then select Filter. In B1, filter to TRUE.
3. Step 2 will give you 1,000 rows as the formula will be true only 1000 times.
4. This is not a macro; a macro would do this all automatically. You did not request this text to be entered in your original post. If you need that as well, I could suggest another couple of steps, but you would probably be better off going for a macro at this point. I'd help, but I'm not good enough to do the second part of what you want yet.

Re: Increment By 1 The Variable in Every 503 Rows

Perhaps this will help you.

I have attached a sample spreadsheet that shows a method of achieving your goal.

create a column to the right with the following formula =IF(CELL("row",A512)=$D$2,$D$2,0)
Where D2 is equal to 512

over one more column place the formula =IF(B512=$D$2,1,0) Format the cells in the column with a custom format that is equal to: "Group" #;[=0]#;General

For row 1024 and beyond the formula for the first column would be =IF(CELL("row",A1024)=(B1024+$D$2),B1024+$D$2,0)

and the second column, =IF(B1024=$D$2+B1024,1+C1024,0)

after you have copied the second 2 formulas all the way down, select the 2 columns and copy the values then delete the first column.

Regards,

-Dude 8-)

P.S. For some reason i put 512, just replace with the correct number

Re: Increment By 1 The Variable in Every 503 Rows

Try this

Option Explicit


Sub GroupsOf500()
    Dim c As Range
    Dim FirstRow As Long


    FirstRow = 1
    For Each c In Range("A1", Range("A600000").End(xlUp))
        If c Mod 500 = 0 Then
            Range(Cells(FirstRow, 1), Cells(c.Row - 1, 1)).Rows.Group
            FirstRow = c.Row + 1
        End If
    Next c


End Sub

Re: Increment By 1 The Variable in Every 503 Rows

ah.. Bill,

Much better, macros are the way to go for this one.
Unfortunately my macro skills are slim to none.

Good Job.

-Dude 8-)

Re: Increment By 1 The Variable in Every 503 Rows

Thanks Dude,

this is the place to learn VBA for sure