Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel column split in 2 (officeXP)

    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"

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel column split in 2 (officeXP)

    Do you always start in the same cell?

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: excel column split in 2 (officeXP)

    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?
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  4. #4
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel column split in 2 (officeXP)

    Hello,
    try this:
    <pre>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</pre>



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

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: excel column split in 2 (officeXP)

    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.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel column split in 2 (officeXP)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •