MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple column problem

Posted by Zef on June 22, 2000 4:18 AM


If anyone can be of assistance with this problem it would be greatly appreciated.

I want to display a list of words (over 1000) alphabetically in a two column per page format. So for
example, the words beginning with A-G would be in Column A of page 1 and H-K would be in
Column B of page 1 then I-P would be in Column A of page 2 and Q-Z in Column B of page 2.(this
is just an example, there may end up being more pages than this). My problem is that when I want
to insert a new entry into, for example, Column A of page 1, the last entry in Column A of page 1
shifts down to the top entry of Column A of page 2 instead of the top entry of Column B of page 1
(and so forth). What do I need to do to make this happen the way I want?

Thanks for your help, and please email me if you need me to clarify anything.

Zef :-)

Posted by mads on June 25, 0100 11:11 AM

Try the following.
It should arrange the data into two columns consisting of 50 rows per page. Just change the variable settings to fit what you need.
If you want it to run automatically whenever the data is changed, it could be called from a Worksheet_Change procedure.

Sub TwoColumns()
Dim Cell1A As Range, Cell1B As Range
Dim rngA As Range, rngB As Range
Dim dataSet As Range, break As Range
Dim ttlRows As Integer, ttlRowsX2 As Integer
'First cell in first column of data
Set Cell1A = Range("A1") '***
'Range of data in first column
Set rngA = Range(Cell1A, Range("A65536").End(xlUp)) '***
'First cell in second column of data
Set Cell1B = Range("B1") '***
'Range of data in second column
Set rngB = Range(Cell1B, Range("B65536").End(xlUp)) '***
'First set of data in first column to