Excel-VBA: Split text in single cell into multiple rows with origin cell dependent on filter

phiman13

New Member
Joined
Jun 1, 2018
Messages
2
Dear all,

this is my first post on this board, hi everyone!
I have been struggling with an issue for two days now, and so far haven't been able to find a solution. Also, I'm a total VBA beginner, so any help is greatly appreciated!

I have two sheets in my workbook: The first is an output dashboard, the second a data table.
The data is structured in the following way:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]2016[/TD]
[TD]2015[/TD]
[TD]2014[/TD]
[TD]2013[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]58-XYZ,59-ABC,...[/TD]
[TD]52-DEF,48-CDE,...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
On my dashboard sheet, I've set up a dropdown filter menu to choose the respective year of interest for this issue as well as some other analyses (2016,2015,2014,2013,2012).
Now to the issue at hand: I need Excel to select the correct cell from my data sheet, based on the year selected for filtering (e. g. when selecting "2016" from my dropdown, Excel should select cell B2 from the data sheet; for "2015", select C2, etc.).
Next, I need Excel to split up the string in cell B2 (52-DEF,48-CDE,...) into separate strings (comma as seperator) and insert these separate strings as distinct rows into my output table like so:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]52-DEF[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]48-CDE[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]
To top things off, the output table should appear to automatically expand based on how many separate values were pulled from the source cell.
For this, I've already successfully employed the following code within another part of the worksheet:

Code:
Sub HideUnhide()
    Dim LEB As Range
    Application.ScreenUpdating = False
        For Each LEB In Range("D56:D145")
            If LEB.Value = 0 Then
                LEB.EntireRow.Hidden = True
                     Else
                LEB.EntireRow.Hidden = False
            End If
        Next LEB
    Application.ScreenUpdating = True
End Sub
Any help putting all this together into VBA code would be greatly appreciated!

Cheers
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top