Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADDRESS MACRO
i have a list of data. all the info is in column a. some info has three
lines some four, some may have more. i need a macro of some sort that would take all of the data and put it into separate colums. an example is this: name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx accounting Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx NEEDS TO LOOK LIKE THIS name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx OBVIOUSLY WITH THE LAST COLUMN IN THERE AS WELL. CITY STATE AND ZIP CAN BE ALL IN ONE COLUMN. PLEASE HELP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADDRESS MACRO
Try this:
Sub spread_um() n = Cells(Rows.Count, "A").End(xlUp).Row j = 2 k = 1 For i = 1 To n Cells(k, j).Value = Cells(i, 1).Value j = j + 1 If Cells(i, 1).Value = "" Then j = 2 k = k + 1 End If Next End Sub -- Gary''s Student - gsnu200771 "JOSEPH WEBER" wrote: i have a list of data. all the info is in column a. some info has three lines some four, some may have more. i need a macro of some sort that would take all of the data and put it into separate colums. an example is this: name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx accounting Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx NEEDS TO LOOK LIKE THIS name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx OBVIOUSLY WITH THE LAST COLUMN IN THERE AS WELL. CITY STATE AND ZIP CAN BE ALL IN ONE COLUMN. PLEASE HELP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADDRESS MACRO
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, TEST_COLUMN).Value = "" Then .Rows(i).Delete ElseIf .Cells(i - 1, TEST_COLUMN).Value < "" Then .Cells(i - 1, TEST_COLUMN).Value = .Cells(i - 1, TEST_COLUMN).Value _ & " " & .Cells(i, TEST_COLUMN).Value .Rows(i).Delete End If Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JOSEPH WEBER" <JOSEPH wrote in message ... i have a list of data. all the info is in column a. some info has three lines some four, some may have more. i need a macro of some sort that would take all of the data and put it into separate colums. an example is this: name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx accounting Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx NEEDS TO LOOK LIKE THIS name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx OBVIOUSLY WITH THE LAST COLUMN IN THERE AS WELL. CITY STATE AND ZIP CAN BE ALL IN ONE COLUMN. PLEASE HELP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I put an email address in a macro ? | New Users to Excel | |||
Address of workbook in a macro | Excel Discussion (Misc queries) | |||
Change IP address with macro | Excel Programming | |||
Need a macro to Identify a users IP address | Excel Programming | |||
Help Pls re macro cell address | Excel Discussion (Misc queries) |