CODEWITHSUNDEEP
vbaexcel
tyler
tyler

Reputation: 91

VBA to remove commas in a cell and paste the cell values in different rows

I have excel data as shown below in X Codes . X code column has data in diffrent rows. What I need to achieve is as shown in the X Codes(result).

X codes

2222, 3333, 4444, 5555    
3458, 4532, 5463, 8976, 4538, 3244, 4538    
2222, 4532,  3243, 3243 , 3243,  EE44    
WW21, EE33, 4532, 5690, 4573, 6758

X codes(result)

2222    
3333    
4444    
5555    
3458    
4532    
5463    
8976    
4538    
3244    
4538    
2222    
4532    
3243    
3243    
3243    
EE44    
WW21    
4532    
5690    
6758

enter image description here

Upvotes: 0

Views: 789

Answers (2)

A.S.H
A.S.H

Reputation: 29332

Here's a loop-less solution using arrays; join the cells with commas, then split them back:

Sub joinThenSplit()
    Dim ar: ar = Sheet3.Range("A2", Sheet3.Range("A999999").End(xlUp)).Value2
    ar = Split(Join(Application.Transpose(ar), ","), ",")
    Sheet3.Range("C2").Resize(UBound(ar) + 1).Value = Application.Transpose(ar)
End Sub

Upvotes: 2

Brandon Barney
Brandon Barney

Reputation: 2392

To further elaborate on Scott's suggestion, use the Split command. For example 

    Sub Example()
        Dim SomeValue As String
        Dim SplitValue As Variant
        Dim ToSheet As Variant

        SomeValue = ThisWorkbook.Sheets("Sheet3").Range("A2").Value

        SplitValue = Split(SomeValue, ", ")

        ' SplitValue should now look something like:
        ' SplitValue(0) = "2222"
        ' SplitValue(1) = "3333"
        ' SplitValue(2) = "4444"
        ' SplitValue(3) = '5555"

        ' Now you can easily put these values into the next available spot on your sheet
        ' Here I use Range("C1000000") because it is very close to the max row count
        ' There are other ways of doing this as well.

        ' What is happening in the code below is I am finding my next entry location, and then
        ' resizing this to include the number of additional rows I need (the max dimension of
        ' the splitvalue array + 1 since Split creates 0 based arrays)

        ' I use a second array that is 2D so that I can directly assign the values from the array to the sheet range
        ' instead of assigning each cell value individually
        ReDim ToSheet(1 To UBound(SplitValue) + 1, 1 To 1)
        Dim i As Long
        For i = LBound(SplitValue) To UBound(SplitValue)
            ToSheet(i + 1, 1) = SplitValue(i)
        Next
        ThisWorkbook.Sheets("Sheet3").Range("C1000000").End(xlUp).Resize(UBound(ToSheet)).Value = ToSheet
End Sub

This should get you headed in the right direction. This wont do all of it for you, but see if you can figure out how to use this as a framework for building the routine you need. Split is a super useful function, and used with a loop could give you exactly what you need.

Best of luck.

Upvotes: 0

Related Questions