paneliyadhruv
Member
Dear Sir,
I got macro which can split column based on value.
Columns to be split (as in trail workbook).
Range of columns to be split from EA to FX.
No of time (no of times) to be copied from first column is in B1 for EA, d1 for EB and so on.
Out put for each split column start from column GA.
Means move input range one column each time (EA till FX).
Values for splitting column B1, d1, f1, h1 till CY1,
Output start from GA.
I have marked yellow color for output of two macros.
Thank you very much for your help.
I got macro which can split column based on value.
Columns to be split (as in trail workbook).
Range of columns to be split from EA to FX.
No of time (no of times) to be copied from first column is in B1 for EA, d1 for EB and so on.
Out put for each split column start from column GA.
Means move input range one column each time (EA till FX).
Values for splitting column B1, d1, f1, h1 till CY1,
Output start from GA.
I have marked yellow color for output of two macros.
Code:
Private Sub SplitColumn1()
Dim rng As Range
Dim InputRng As Range
Dim OutRng As Range
Dim xRow As Integer
Dim xCol As Integer
Dim xArr As Variant
Dim i As Integer
Dim xvalue As Variant
Dim iRow As Integer
Dim iCol As Integer
On Error Resume Next
Set InputRng = Sheets("Sheet1").Range("EA1:EA2200")
xRow = Sheets("Sheet1").Range("b1")
Set OutRng = Sheets("Sheet1").Range("EC1")
Set InputRng = InputRng.Columns(1)
xCol = InputRng.Cells.Count / xRow
ReDim xArr(1 To xRow, 1 To xCol + 1)
For i = 0 To InputRng.Cells.Count  1
xvalue = InputRng.Cells(i + 1)
iRow = i Mod xRow
iCol = VBA.Int(i / xRow)
xArr(iRow + 1, iCol + 1) = xvalue
Next
OutRng.Resize(UBound(xArr, 1), UBound(xArr, 2)).Value = xArr
End Sub
Thank you very much for your help.
Attachments

33.6 KB Views: 9
Last edited: