excel column split in 2 (officeXP)

Topic

New Reply

i have a column of figures which is spread over 3 columns and anyware from 200 to 2000 rows, can any one suggest a method of splitting the no of rows in half and move it to the top row but to cell “E2”

Reply | Quote

Replies

Do you always start in the same cell?

Reply | Quote

Do you always start in the same cell?

Reply | Quote

Do you want the total count of populated rows in each column entered in cell E2? Or do you want to move the data so the rows are half as many, if so, do you want all that data in column E starting at cell E2? Is there some additional object to your request?

Reply | Quote

Do you want the total count of populated rows in each column entered in cell E2? Or do you want to move the data so the rows are half as many, if so, do you want all that data in column E starting at cell E2? Is there some additional object to your request?

Reply | Quote

Hello,
try this:

Sub HalfRange()

RowsCnt = Range([a2], [a65536].End(3)).Rows.Count
FstRowToMove = RowsCnt  2 + 1

Set srcRange = Range("A" & FstRowToMove).Resize(RowsCnt - FstRowToMove + 2, 3)
Set trgRange = [e2].Resize(RowsCnt - FstRowToMove + 2, 3)

srcRange.Copy trgRange
srcRange.Clear

End Sub

It’s an air-code, not tested. I assume you start in [A2], otherwise adjust +1 and +2 in the code.

Reply | Quote

Hello,
try this:

Sub HalfRange()

RowsCnt = Range([a2], [a65536].End(3)).Rows.Count
FstRowToMove = RowsCnt  2 + 1

Set srcRange = Range("A" & FstRowToMove).Resize(RowsCnt - FstRowToMove + 2, 3)
Set trgRange = [e2].Resize(RowsCnt - FstRowToMove + 2, 3)

srcRange.Copy trgRange
srcRange.Clear

End Sub

It’s an air-code, not tested. I assume you start in [A2], otherwise adjust +1 and +2 in the code.

Reply | Quote

This version replaces my earlier post which had bugs. It will move anything out of bottom half of the first three columns into Column E without losing any data:

Sub halfrows()
Dim intC As Integer
Dim lngRowTrim As Long
For intC = 1 To 3
lngRowTrim = Int(Cells(65536, intC).End(xlUp).Row / 2)
Range(Cells(65536, intC).End(xlUp), Cells(lngRowTrim + 1, intC)).Cut _
[E65536].End(xlUp).Offset(1, 0)
Next intC
End Sub

If you need, a more generic version can be written which will move anything out of any number of selected columns to the next unused column.

Reply | Quote

whilst i appreciat4e every ones help i have been given the following solution which works perfectly.
sub SplitCopyPaste
dim LastRow as interger
LastRow=sheets(“sheet1”).UsedRange.Rows.count
‘ (1) LastRow / 2 gives the centre position of the column
‘ (2) ((LastRow/2)+1,1) the +1 is to make the columns equal in length
‘ (3) (LastRow,3) the “3” represents the noumber of columns
‘ (4) Range(“E2”) is the position of the row for the second part of the split range
Range(Cells(Int(LasRow/2)+3,1, Cells(LastRow,3).Cut Destination:=Range(“E2”)
end sub
it dose work having tried it , and it might be of help to some one else whose looking for a solution.

Reply | Quote

whilst i appreciat4e every ones help i have been given the following solution which works perfectly.
sub SplitCopyPaste
dim LastRow as interger
LastRow=sheets(“sheet1”).UsedRange.Rows.count
‘ (1) LastRow / 2 gives the centre position of the column
‘ (2) ((LastRow/2)+1,1) the +1 is to make the columns equal in length
‘ (3) (LastRow,3) the “3” represents the noumber of columns
‘ (4) Range(“E2”) is the position of the row for the second part of the split range
Range(Cells(Int(LasRow/2)+3,1, Cells(LastRow,3).Cut Destination:=Range(“E2”)
end sub
it dose work having tried it , and it might be of help to some one else whose looking for a solution.

Reply | Quote

This version replaces my earlier post which had bugs. It will move anything out of bottom half of the first three columns into Column E without losing any data:

Sub halfrows()
Dim intC As Integer
Dim lngRowTrim As Long
For intC = 1 To 3
lngRowTrim = Int(Cells(65536, intC).End(xlUp).Row / 2)
Range(Cells(65536, intC).End(xlUp), Cells(lngRowTrim + 1, intC)).Cut _
[E65536].End(xlUp).Offset(1, 0)
Next intC
End Sub

If you need, a more generic version can be written which will move anything out of any number of selected columns to the next unused column.

Reply | Quote