CODEWITHSUNDEEP
excelvba
Glyph1017
Glyph1017

Reputation: 3

VBA: Cut Substring and Insert to New Row or TTC+Transpose+Insert Rows

I'm attemping to write a VBA module that will search through cell range A1:E400 in a Worksheet:

An example of the dataset I'm working with, as well as the ideal output is included below:

Data Set & Sample Output

My current VBA Module looks like this:

Sub FindDupes()
Dim rng As Range
Dim Strng As String
Set rng = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
If Not rng Is Nothing Then
    rng.Select
Else
End If

This gets me as far as finding cell values with the "," value and selecting them, but I feel like I am biting off more than I can chew here. I'm not very experienced with VBA and would love some help, I understand this is likely pretty complex..

Something I have oconsidered as a potentially easier approach would be to Text-To-Columns and vertically transpose, but ideally there would be a way for the values to insert the required rows, then paste into the empty cells as required (overwriting the cells with "," in them), e.g:

Sub Vertical()
Dim i As Long, st As String
i = 1
Dim startP As Range
For Each r In Selection
    If i = 1 Then
        st = r.Text
        i = 2
    Else
        st = st & "," & r.Text
    End If
Next r

Set startP = Selection(1, 2)
ary = Split(st, ",")
i = 1
For Each a In ary
    startP(i, 1).Value = a
    i = i + 1
Next a End Sub

Any thoughts?

Upvotes: 0

Views: 188

Answers (1)

Max
Max

Reputation: 759

something like - not tested!

dim max_commas as integer
dim count_commas as integer
    For Each zelle In rngRowChosen
       count_commas = Len(string)-Len(Replace(string,",",""))
       if count_commas > max_commas then max_commas = count_commas 
    Next

insert as many rows as max_commas (replace "4" with the row number where this should go)

either

row_insert_Start = 4
Rows(row_insert_Start:row_insert_Start+max_commas).Insert

or

row_insert_Start = 4
Range("A"&row_insert_Start&":A"&row_insert_Start+max_commas).EntireRow.Insert

Upvotes: 0

Related Questions