Nicole has attended:
Access Advanced course
Access VBA course
VBA Row splitting into columns Excel
Hi there.
Thanks for taking the time to read this.
I have a large data set (10,000 records) that needs every 500th row to be put into columns.
Not sure where to start with the VBA code.
Can you help
Thanks in advance
Nicole
RE: VBA Row splitting into columns Excel
Hi Nicole
Sounds interesting. Do you want to copy each 500th row into another sheet as a column?
In my example I have data in cells A1:H10000.
The macro transposes every 500th row from Sheet1 to Sheet2.
Sub Copy500()
Dim Rownum As Long
Application.ScreenUpdating = False
Rownum = 500
Sheets("Sheet1").Select
Range("A500").Select
Do Until ActiveCell = ""
ActiveCell.Range("A1:F1").Copy
Sheets("Sheet2").Select
Range("A10000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
ActiveCell.Offset(500, 0).Select
Rownum = Rownum + 500
Loop
Application.ScreenUpdating = True
End Sub
May help to say more about what to do with each 500th record. Thanks.
Regards
Doug Dunn
Best STL
RE: VBA Row splitting into columns Excel
Hi Doug,
Thanks for responding. Apologies I was in a rush :)
One column which is 10,000+ records long.
This column is to be split up into rows of 500 and placed into different columns in another sheet.
Example
1
2
3
4
5
6
1 3 5
2 4 6
Hope this makes more sense
RE: VBA Row splitting into columns Excel
Hi Nicole
Thanks, I understand now! (please ignore the last reply)
Here is the macro code that will divide up numbers 1 to 10,000 (in cells A1:A10000) on Sheet1 into column blocks of 500 numbers on Sheet2 (in columns A:T)
Sub CopyCols()
Dim RowNum As Long
Sheets("Sheet1").Select
For RowNum = 1 To 10000 Step 500
Range("A" & RowNum & ":A" & RowNum+499).Select
Selection.Copy
Sheets("Sheet2").Select
Range("AF1").Select
Selection.End(xlToLeft).Select 'same as ctrl left
ActiveCell.Offset(0, 1).Select 'select 1 cell to right
ActiveSheet.Paste
Sheets("Sheet1").Select
Next RowNum
Sheets("Sheet2").Columns("A").Delete ' delete A Col
End Sub
How the macros works
The first 500 cells in Sheet1 are first copied and pasted into B1 on Sheet2. Then with the help of the RowNum variable the next 500 cells are copied (501 to 1000). By changing RowNum in steps of 500 the process continues till all data is copied.
Hope this helps with your actual example.
Doug Dunn
Best STL
