Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
separate multiline address into separate columns
I have an excel file where I need to manipulate the billing street and
shipping street fields. The billing and shipping street field might have two or three lines...i.e.: Archdiocese Of Newark School 2 Cedar Street or Kokomo Center Township C.S.D. 100 W. Lincoln Road P.O. Box 2188 I want to separate each line into its own column. Some of the rows have empty columns for the address. I tried running a macro using the following subroutine: Sub SplitThem() Dim LastR As Long Dim Counter As Long Dim arr As Variant Dim arr2 As Variant LastR = Cells(Rows.Count, 1).End(xlUp).Row arr = [a1].Resize(LastR, 1).Value Worksheets.Add For Counter = 1 To LastR arr2 = Split(arr(Counter, 1), Chr(10)) Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Next End Sub I get an error that occurs on row 7004, which is thrown on the following line when I run the macro: Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Run-time Error '1004': Application-defined or object-defined error The macro worked perfectly until row 7004, then it threw the error because the next row was blank. I copied all the contents of the billing street into a new worksheet and ran the macro, which parsed the data out into three new fields up to row 7004, then it bombed. Thanks in advance for any help. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change address, city, state zip code into separate columns | Excel Discussion (Misc queries) | |||
I wish to separate city, state, and zip into 3 separate columns | Excel Worksheet Functions | |||
separate name and address | Excel Worksheet Functions | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
Separate address column to Five columns | Excel Worksheet Functions |