colum counting and split into two halves 1

[alexanderd]

any one any ideas on how to split a colum of upto 10,000 rows into two halves so that the bottom halve can be selected and moved to another position or work sheet

 

[SkipVought]

Hi,

Range("A5001:A10000").copy Destination:=Range("B1")

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[ETID]

JUST TO ADD A LITTLE TO SKIPS REPLY....



Sub SPLIT_COPY_PASTE()

Dim LAST_ROW As Integer

LAST_ROW = Sheets("sheet1&quot.UsedRange.Rows.Count

Range(Cells(Int(LAST_ROW / 2) + 1, 1), Cells(LAST_ROW, 1)).Cut Destination:=Range("B1&quot

End Sub

 

[alexanderd]

i feel that i have missled you in one respect that (and this is my fault) the range consists of 3 x colum's and up to 10,000 rows long

 

[SkipVought]

Range("A5001:C10000").copy Destination:=Range("D1")

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[alexanderd]

thank you for your input(ETID & SkipVought) i have tweaked it and it works exactly as i want it to
Option Explicit

Sub SPLIT_COPY_PASTE()

Dim LAST_ROW As Integer

LAST_ROW = Sheets("sheet1&quot.UsedRange.Rows.Count

' (1) LAST_ROW / 2 gives the centre position of the column

' (2) (LAST_ROW / 2) + 1, 1) the + 1 is to make the colums equal in length

' (4) ( LAST_ROW , 3 ) the 3 represents the no. of Columns

' (5) Range ( "E5&quot is the position of the row for the 2nd part of the split range

Range(Cells(Int(LAST_ROW / 2) + 3, 1), Cells(LAST_ROW, 3)).Cut Destination:=Range("E5&quot

End Sub

 

[alexanderd]

hi just to let you know that this works ok for me but with reservations. i have two sheets both with the same format either 3 columns or 4 columns wide.

The 4 column wide works a treat but the 3 column on is perculiar in that some times it only takes approx the bottom 1/3rd of the column and moves it to a new posion .

The two sheets are in the same work book and driving me mad, any help or pointers to resolve this would be appreciated

 

[SkipVought]

alex,

See my in-code comments...

Sub SPLIT_COPY_PASTE()
'get in the habit of defining ROW variables as LONG _
since INTEGER only gives you 32767 and there are over 65000 rows in Excel
Dim LAST_ROW As Long

'if the used range does NOT begin in ROW 1, the former formula will give you and incorrect last row _
--consider a used range with data in rows 5 & 6 _
Rows.Count is 2.  But Last row should be 2 + 5 - 1
With Sheets("sheet1").UsedRange
    LAST_ROW = .Rows.Count + .Row - 1
End With

' (1)  LAST_ROW / 2 gives the centre position of the column

' (2)  (LAST_ROW / 2) + 1, 1)  the + 1 is to make the colums equal in length

' (4) ( LAST_ROW , 3 ) the 3 represents the no. of Columns

' (5) Range ( "E5") is the position of the row for the 2nd part of the split range

Range(Cells(Int(LAST_ROW / 2) + 3, 1), Cells(LAST_ROW, 3)).Cut Destination:=Range("E5")

End Sub

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[alexanderd]

thank you for a quick response, whilst i am able to say that the solution works exceptionally well in one instance. in a second instance using the same module but to take into account that the sheet in this case only had 3 columns, only a third of the file was moved to the final position in cell "E2" iwas going to send a copy of the file for your comments can not see how to go about it on this site

 

[SkipVought]

Send to

skip @ theofficeexperts.com

but remove the spaces



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com