Excel macro - split table/column in half

**ron431800** · 03-10-2011, 09:22 PM

Hi All,

As this is my first post I will give a bit of an introduction first! I have been playing with excel macros over the last few months to make some of my reporting and data analysis tasks easier and more efficient. With the help of this forum I have had some great success while learning a lot as I go.

I have searched high and low for a way to split a table/column in half but cannot find anything. Can anyone shed any light on this?

Basically I have a table that i need to split in half and put each half in a new sheet.

So far I have been unable to find a way to make excel find and go to the middle of the range of cells or column.

Thanks in advance

**MarvinP** · 03-10-2011, 09:35 PM

Hi Ron and welcome to the forum.

If you have a table that starts in A1 and has many rows and many columns, I think your question is how do you find the column number that is half of the total?

If you table starts in A1 then how would the code:

LastColumn = Cells(1.Columns.Count).End(xlToLeft).Column

This would give the last column with data in Row 1.

If you insert a blank column at half LastColumn, would that split your table in half?

**ron431800** · 03-11-2011, 02:41 AM

Thanks Marvin!

Yes, so i guess my aim would be to jump to the 'half LastColumn' cell in column 1.

So then when I get to that cell, I can highlight the rest of the table (bottom half) and move this content to the next sheet.

Does that make sense?

**ron431800** · 03-15-2011, 12:17 AM

Bump - still looking for a solution to this.

Thanks,

Ron

**MarvinP** · 03-15-2011, 12:35 AM

Hi Ron,
I thought you wanted to split Right and Left halves. You said "BOTTOM" that makes my assumption pretty silly.

Half way down the rows is

Please Login or Register  to view this content.

Then divide this number by 2 and that is half way down your rows. Better round it to an integer.

If you supply a sample workbook it is much easier for us to answer these questions. You can click on the Go Advanced below the message area and the click on the Paper Clip Icon above the message area to attach a sample file.

Instead of splitting up your data, how about just splitting up the screen as in
http://gaetc-ejournal.org/tutorials/...tion/split.htm