How do I make text to columns automated?

Sub ConvertCSVToTable()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim tbl As ListObject

' Set the active sheet as the target
Set ws = ActiveSheet

' Find the last row and column with data
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Select the data range
ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Select

' Perform Text to Columns with semicolon delimiter
Selection.TextToColumns _
Destination:=ws.Cells(1, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False

' Recalculate last column after Text to Columns
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Format the resulting range as a table
Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)), , xlYes)
tbl.TableStyle = "TableStyleMedium2" ' Adjust the table style as needed

MsgBox "Data delimited and formatted as a table."
End Sub

Shakeh93 Sub ConvertCSVToTable()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim tbl As ListObject

' Set the active sheet as the target
Set ws = ActiveSheet

' Find the last row and column with data
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Check if there is any data to process
If lastRow > 1 And lastCol > 1 Then
' Select the data range
ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Select

' Perform Text to Columns with semicolon delimiter
Selection.TextToColumns _
Destination:=ws.Cells(1, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False

' Recalculate last column after Text to Columns
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Format the resulting range as a table
Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)), , xlYes)
tbl.TableStyle = "TableStyleMedium2" ' Adjust the table style as needed

MsgBox "Data delimited and formatted as a table."
Else
MsgBox "No data found to convert."
End If
End Sub

Shakeh93 

Hi 

You might consider using Power Query. It's one single step for splitting at a Delimiter and send the data back to Excel . Sample attached.

Steps:

• Convert your list into a Table (CTRL + T)
• Click on the Data Tab and Select : "From Table" >> The Query Editor Opens
• On the Home Tab Click on "Split Columns" >> Select By Delimiter ", " and each occurrence
• On the Home Tab >> Close and Load

When source data Changes, just hit Refresh.

 Hope That Helps

Nabil Mourad

Shakeh93 

Hi, just so I understand:

First you have this

next you want to have this

then I'm not sure I understand. Is the next step that you add more comma separated text in row 2 like this?

And what would be the next step?

Option 1:

or option 2: