Results 1 to 7 of 7

Split Delimited Data in Columns into New Rows and keep all other data

Threaded View

VBA_123 Split Delimited Data in... 10-07-2020, 06:03 AM
nigelog Re: Split Delimited Data in... 10-07-2020, 07:12 AM
VBA_123 Re: Split Delimited Data in... 10-07-2020, 07:20 AM
nigelog Re: Split Delimited Data in... 10-07-2020, 07:28 AM
VBA_123 Re: Split Delimited Data in... 10-07-2020, 07:33 AM
VBA_123 Re: Split Delimited Data in... 10-07-2020, 10:35 PM
alansidman Re: Split Delimited Data in... 10-08-2020, 05:22 AM
  1. #1
    Registered User
    Join Date
    10-07-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Split Delimited Data in Columns into New Rows and keep all other data

    HI,

    I'm new to VBA and I am trying to split the data in my columns into separate rows while keeping the 1st column as well as the header intact. I have an existing code which allows me to split column B but I'm trying to edit the code such that I am able to split the data starting from Column B all the way to the last column. Can anyone kindly advice on how to do that? I've attached the excel file with the data as well as the below macro already in there.

    Thank you and I sincerely appreciate the help!

    ____________________________________________________________________________________________________________________________

    Option Explicit
    
    Const ANALYSIS_ROW As String = "B"
    Const DATA_START_ROW As Long = 1
    
    Sub ReplicateData()
        Dim iRow As Long
        Dim lastrow As Long
        Dim ws As Worksheet
        Dim iSplit() As String
        Dim iIndex As Long
        Dim iSize As Long
    
        'Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
    
        With ThisWorkbook
            .Worksheets("Concertina").Copy After:=.Worksheets("Concertina")
            Set ws = ActiveSheet
        End With
    
        With ws
            lastrow = .Cells(.Rows.Count, ANALYSIS_ROW).End(xlUp).Row
        End With
    
    
        For iRow = lastrow To DATA_START_ROW Step -1
            iSplit = Split(ws.Cells(iRow, ANALYSIS_ROW).Value2, ",")
            iSize = UBound(iSplit) - LBound(iSplit) + 1
            If iSize = 1 Then GoTo Continue
    
            ws.Rows(iRow).Copy
            ws.Rows(iRow).Resize(iSize - 1).Insert
            For iIndex = LBound(iSplit) To UBound(iSplit)
                ws.Cells(iRow, ANALYSIS_ROW).Offset(iIndex).Value2 = iSplit(iIndex)
            Next iIndex
    Continue:
        Next iRow
        Application.CutCopyMode = False
        Application.Calculation = xlCalculationAutomatic
        'Application.ScreenUpdating = True
        
        
    End Sub
    Attached Files Attached Files
    Last edited by alansidman; 10-08-2020 at 05:22 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. split delimited data into rows - how???
    By elizevonne in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-31-2020, 06:25 PM
  2. [SOLVED] Split data from columns to rows
    By tax112 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-04-2019, 05:44 AM
  3. [SOLVED] Split Delimited nth Data into New Rows/columns
    By adeel1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-29-2018, 01:55 AM
  4. Vba code to split data with delimited "," in separate rows
    By gaikwad.mm@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-26-2017, 02:26 AM
  5. Delimited Data - Columns and Rows
    By Arch88 in forum Excel General
    Replies: 1
    Last Post: 06-01-2016, 07:11 AM
  6. [SOLVED] Multi columns of delimited data into rows
    By DWTK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2013, 11:18 AM
  7. Split one row of data to columns and rows
    By A7md in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-12-2011, 05:52 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1