Count rows, divide by three, copy 1/3 to to each column

[newbieX]

I have spreadsheets that are created on the fly that can have 1 to hundreds of rows. There is always only just one column. I need to create 3 columns in the active spreadsheet from these results using VBA. So I need to count the number of rows, divide this number by 3, and put the second third in column B, and the final third in column 3. If the total rows is not exactly divisible by 3, then the higher counts go in the leftmost column.

Thus if there are 99 rows, Column A would contain rows 1-33, Column B 34-66, Column C 67-99.
If there were 100 rows, Column A would contain rows 1-34, Column B 35-67, and Column C 68-100.
If there were 101, Column A would contain rows 1-34, Column B 35-68, Column C 69-101.

Additional, if there was 1 row it would go in Column A, 2 rows, 1 record in Column A and 1 in Column B.

I have no idea as to how to accomplish this. Suggestions anyone?

I know I can count rows with this formula but that is about it.

LastRow = ActiveSheet.UsedRange.Rows.Count

Thanks VBA gurus.

Excel 2007

 

[DeBeuz]

Hi,

What comes to my mind is using different counts for the copy steps:

    Total = ActiveSheet.UsedRange.Rows.Count
    Crows = Int(Total / 3)
    Brows = Int((Total - Crows) / 2)

So you need to move the last Crows rows to column C.
After that you need to move Brows to column B.
Automatically the remaining rows are left in column A.

Does this help you?

Paul

 

[Comfy]

Give this a shot:

Sub RangeMove()


Dim RngA As Range, RngB As Range, RngC As Range
Dim LastRow As Long
Dim RngArow As Integer
Dim rngRows As Integer


LastRow = ActiveSheet.UsedRange.Rows.Count
rngRows = CInt(LastRow / 3)
RngArow = CInt(LastRow / 3) + (LastRow Mod rngRows)


Set RngA = Range(Cells(1, 1), Cells(RngArow, 1))
Set RngB = Range(Cells(RngArow + 1, 1), Cells(RngArow + rngRows, 1))
Set RngC = Range(Cells(RngArow + rngRows + 1, 1), Cells(LastRow, 1))


RngB.Cut Destination:=Range("B1")
RngC.Cut Destination:=Range("C1")


End Sub

 

[newbieX]

Yes the code worked great. I needed to do some additional formatting to the table so I added the following code to select the new area since my go to command

Range("A1", ActiveCell.SpecialCells(xlLastCell))

selected the wrong area now. I'm tickled I figured it out.

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:C" & LastRow).Select

Thanks a bunch. I love that I get great help from all over the world.

Give this a shot:

Sub RangeMove()


Dim RngA As Range, RngB As Range, RngC As Range
Dim LastRow As Long
Dim RngArow As Integer
Dim rngRows As Integer


LastRow = ActiveSheet.UsedRange.Rows.Count
rngRows = CInt(LastRow / 3)
RngArow = CInt(LastRow / 3) + (LastRow Mod rngRows)


Set RngA = Range(Cells(1, 1), Cells(RngArow, 1))
Set RngB = Range(Cells(RngArow + 1, 1), Cells(RngArow + rngRows, 1))
Set RngC = Range(Cells(RngArow + rngRows + 1, 1), Cells(LastRow, 1))


RngB.Cut Destination:=Range("B1")
RngC.Cut Destination:=Range("C1")


End Sub

 

[Comfy]

Yes the code worked great. I needed to do some additional formatting to the table so I added the following code to select the new area since my go to command

Range("A1", ActiveCell.SpecialCells(xlLastCell))

selected the wrong area now. I'm tickled I figured it out.

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:C" & LastRow).Select

Thanks a bunch. I love that I get great help from all over the world.

Rather than finding what the lastrow is again you could use an object that already holds that info.

Range("A1:C" & RngArow).Select

 

[newbieX]

I thought the code worked but it turns out that it does not work if the row count = 1, 2, 4, 5, 8, 11, 14, 17, 17+3 etc. Works for all other numbers. Is it because decimal = .666...? Since my row count will always be > 9, I am not concerned with error messages associated with 1 or 2 rows. What is happening is that the 3rd column (Column C) is not getting written to. Thus is there are 11 rows, after running the macro Column A has 7 records and B has 4. Ideas?

 

[Comfy]

Yep, as you pointed out it was due to rounding, my mistake.

Try this:

Sub RangeMove()




Dim RngA As Range, RngB As Range, RngC As Range
Dim LastRow As Long
Dim RngArow As Integer
Dim rngRows As Integer




LastRow = ActiveSheet.UsedRange.Rows.count
rngRows = Application.WorksheetFunction.RoundDown(LastRow / 3, 0)
RngArow = rngRows + (LastRow Mod rngRows)




Set RngA = Range(Cells(1, 1), Cells(RngArow, 1))
Set RngB = Range(Cells(RngArow + 1, 1), Cells(RngArow + rngRows, 1))
Set RngC = Range(Cells(RngArow + rngRows + 1, 1), Cells(LastRow, 1))




RngB.Cut Destination:=Range("B1")
RngC.Cut Destination:=Range("C1")




End Sub

 

[newbieX]

The code still gets the numbers referred to in my previous post wrong. Now there are three rows of data but row 1 has 5 records, row 2 has 3 and row 3 has 3. It should be 4,4,3 respectively.

I did study your code and researched the MOD function and then took a slightly different approach. Using your example, I resolved my issue with the following code:

Dim RngA As Range, RngB As Range, RngC As Range
Dim LastRow As Long, RngArow As Integer, rngRows As Integer

LastRow = ActiveSheet.UsedRange.Rows.count
rngRows = Application.WorksheetFunction.Round(LastRow / 3, 0)
RngArow = Application.WorksheetFunction.RoundUp(LastRow / 3, 0)

Set RngA = Range(Cells(1, 1), Cells(RngArow, 1))
Set RngB = Range(Cells(RngArow + 1, 1), Cells(RngArow + rngRows, 1))
Set RngC = Range(Cells(RngArow + rngRows + 1, 1), Cells(LastRow, 1))

RngB.Cut Destination:=Range("B1")
RngC.Cut Destination:=Range("C1")

Thanks for your help. Without your guidance I would have never figured this out.

 

[Comfy]

The code still gets the numbers referred to in my previous post wrong. Now there are three rows of data but row 1 has 5 records, row 2 has 3 and row 3 has 3. It should be 4,4,3 respectively.

I'm glad you worked it out but this is not what you specified.

"So I need to count the number of rows, divide this number by 3, and put the second third in column B, and the final third in column 3. If the total rows is not exactly divisible by 3, then the higher counts go in the leftmost column. "

i.e. 5 3 3