+ Reply to Thread
Results 1 to 9 of 9

Text to Column with Single Column Transpose - VBA code help

  1. 08-13-2014, 01:57 PM #1
    i2rule
    i2rule is offline
    Registered User
    Join Date
    02-11-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Text to Column with Single Column Transpose - VBA code help

    I have a file that I need to do a text to column separated by comma and then transpose results to a single column. See exaple below

    File
    Area Zone
    08865 18
    18013 15
    18014 13
    18015 24, 25
    18042 14, 16, 18


    Results:

    Area Zone
    08865 18
    18013 15
    18014 13
    18015 24
    18015 25
    18042 14
    18042 16
    18042 18
    Last edited by i2rule; 08-14-2014 at 09:52 AM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 08-13-2014, 02:40 PM #2
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Text to Column with Single Column Transpose - VBA code help

    Maybe:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 08-13-2014, 02:58 PM #3
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,552

    Re: Text to Column with Single Column Transpose - VBA code help

    Try
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 08-13-2014, 04:00 PM #4
    i2rule
    i2rule is offline
    Registered User
    Join Date
    02-11-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Text to Column with Single Column Transpose - VBA code help

    I was having a problem with these 2 line
    Range("A2:A" & Range("B" & Rows.count).End(3).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    For Each rcell In Range("A2:A" & Range("B" & Rows.count).End(3).Row)

    But I got it fix thanks for the help
    Reply With QuoteRegister To Reply
  5. 08-13-2014, 04:02 PM #5
    i2rule
    i2rule is offline
    Registered User
    Join Date
    02-11-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Text to Column with Single Column Transpose - VBA code help

    Quote Originally Posted by jindon View Post
    Try
    Please Login or Register  to view this content.


    This also works. What about if I want to specify the column to paste the data?
    Last edited by i2rule; 08-13-2014 at 04:03 PM. Reason: added code
    Reply With QuoteRegister To Reply
  6. 08-13-2014, 04:06 PM #6
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,552

    Re: Text to Column with Single Column Transpose - VBA code help

    Change
    Please Login or Register  to view this content.
    To whatever the the cell you want to paste.
    e.g
    Please Login or Register  to view this content.
    Last edited by jindon; 08-13-2014 at 04:09 PM.
    Reply With QuoteRegister To Reply
  7. 08-13-2014, 04:19 PM #7
    i2rule
    i2rule is offline
    Registered User
    Join Date
    02-11-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Text to Column with Single Column Transpose - VBA code help

    Quote Originally Posted by jindon View Post
    Change
    Please Login or Register  to view this content.
    To whatever the the cell you want to paste.
    e.g
    Please Login or Register  to view this content.
    Thanks for all your help. This will same me alot of time.
    Reply With QuoteRegister To Reply
  8. 08-13-2014, 04:53 PM #8
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Text to Column with Single Column Transpose - VBA code help

    I also wrote one, so, since I spent time on it:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  9. 08-13-2014, 05:09 PM #9
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,552

    Re: Text to Column with Single Column Transpose - VBA code help

    i2rule

    You are welcome.

    Can you mark this thread as "Solved"?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transpose Data from Multiple Column to Single Column
    By smudger1989 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2013, 04:38 AM
  2. [SOLVED] Transpose a single column to a single row with duplicates removed
    By ggilzow in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2013, 10:50 AM
  3. [SOLVED] I need the hide/unhide code to work with text not only numbers and single column
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-23-2012, 08:03 AM
  4. Transpose multiple text-based columns to single column while retaining record ids
    By SchoobsVT in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-06-2010, 12:26 PM
  5. transpose a range to a single column
    By Jeff in forum Excel General
    Replies: 7
    Last Post: 06-15-2006, 09:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1