NEED HELP - Convert Rows to Columns

  • Thread starter Thread starter ck_sales
  • Start date Start date
C

ck_sales

I need help here. This will save me a ton of work if it can be done.
want to know if I can place every 4th row into the same column bu
different rows.

For example:

PERSON 1
1500 Ellsworth Ave # 204
Heidelberg PA 15106-3950
--------------------------------------------------------------------------------

PERSON 2
220 Penn Ave
Scranton PA 18505
--------------------------------------------------------------------------------

PERSON 3
101 Gibraltar Rd #150
Horsham PA 19044
--------------------------------------------------------------------------------

And get the data to look like this:

PERSON 1 1500 Ellsworth Ave # 204 Heidelberg PA 15106
PERSON 2 220 Penn Ave Scranton PA 18505
PERSON 3 101 Gibraltar Rd #150 Horsham PA 19044


Any help on this would be great!!!

Thank
 
Well, I know I would use a simple macro for this. I would record th
keystrokes for rearranging one set, i.e., 3 rows, and include droppin
down to the first row of the next set in the macro. Assign a key t
the macro when creating it. Then, just put the cell pointer on
starting point which would be any first row of a set, e.g., PERSON 1
PERSON 2, etc., and just hold the keys down until the macro finishe
the entire batch of records
 
how do I add the drop down to next set of date??? my micro seems t
only go over the first person everytim
 
OK, I think you need to set Relative Reference when you record you
macro. As soon as you start the Macro recorder and you see the littl
two-button Stop Recording toolbar floating over your sheet (or may hav
it position in the toolbar area of Excel), hover over the right butto
until the text box appears with the label. It should say "Relativ
Reference". Click on it, then proceed to record the macro.

Assuming you have your cell pointer positioned on PERSON 1 when yo
start recording the macro, move you cell pointer to PERSON2 as the las
step in the recording procedure.

Now, you should have a macro which will do what I said in my firs
post
 
ck

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error GoTo endit
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
endit:
End Sub

Copy/paste the code to a general module in your workbook. Hit ALT + F11
to get to the Visual Basic Editor. View>Project Explorer. Left-click on your
workbook/project. Insert>Module. Paste in here.

ALT + F11 to go back to Excel. Tools>Macro>Macros. Select the macro by name
then Run.

NOTE: When asked "how many columns" enter 3.

Remember also. There is no "Undo" from a macro. Make sure you try this on a
copy of the worksheet first.

Gord Dibben Excel MVP
 
Back
Top