CODEWITHSUNDEEP
vbaexcel
Tony
Tony

Reputation: 75

How do I trim unused commas in a string with VBA

I am writing code for a command button on an excel sheet that will take data from an input sheet and transfer it to a different workbook template.

I have 6 possible input sections (in one section) x1-x6 that a user can put data in. When I hit the command button I want this to copy and then paste, in a designated cell on a different sheet, the data from those 6 inputs (if there is any).

Here is where my question comes in: I have the copy & paste thing figured out and the concatenate part but what I am not able to figure out is how to remove the extra commas if a user only put in x1 and x2 and leaves the rest blank.

Basically I'm trying to trim the commas from the end of the line and stopping at the first non-comma character, does that make sense? I'll put the code and sample output in to help out 

Private Sub CommandButton1_Click()
Dim x1, x2, x3, x4, x5, x6, X As String
Dim HData As Worksheet
Dim Sdata as Workbook

Worksheets("Information").select
x1 = Range("B1").Value
x2 = Range("B2").Value
x3 = Range("B3").Value
x4 = Range("B4").Value
x5 = Range("B5").Value
x6 = Range("B6").Value
X = x1 + ", " & x2 + ", " & x3 + ", " & x4 + ", " & x5 + ", " & x6

'lots of code between here and the next part'

.Offset(1,1) = X

OUTPUT sample1, sample2, , , , <-- I dont want these commas to show up if there is no data entered in the input section for those cells (or no commas at all if nothing is entered)...

Upvotes: 2

Views: 5816

Answers (3)

katesporks
katesporks

Reputation: 31

Sorry, I don't have Excel installed on the machine I'm currently on, so my syntax may need a little tweaking as I can't test it. You basically have two options.

The first is some muddled algorithmic work.

'Strip leading commas
Do While InStr(X,", ") == 1
    X = RIGHT(X,LEN(X)-2) ' remove the first two characters
Loop
'Remove trailing commas
Do While InStrRev(X,", ") == LEN(X) - 1 ' check the math on this one
    X = LEFT(X,LEN(X)-2)
Loop
'Remove internal extra commas
Do While InStr(X,", , ") > 0
   X = REPLACE(X, ", , ", ", ")
Loop

EDITED: The REPLACE function should be in a loop as REPLACE is not run recursively on the code. Before, "X, , , , X" would only reduce to "X, , X, "

A better solution would be to generate X without the extra commas.

X = ""
For row = 1 to 6
    x = Range("B" & i).Value
    If LEN(X) > 0 Then
        If LEN(X) == 0 Then
           X = x   ' fencepost solution
        Else
           X = X & ", " & x
        End If
    End If
Next i

Upvotes: 2

Alex K.
Alex K.

Reputation: 175776

Loop, if not empty add to a string and assign a delimiter for subsequent pre-pending:

Dim X As String, delimiter As String, cell As Range
For Each cell In Range("B1:B6")
    If (Len(cell.Value) > 0) Then
        X = X & delimiter & cell.Value
        delimiter = ", "
    End If
Next

Debug.Print X

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6206

Try this (Updated for missing cells inside the range):

Private Sub CommandButton1_Click()
    Dim X As String, HData As Worksheet, Sdata As Workbook
    Worksheets("Information").Select
    X = Join(Application.Transpose(Range("B1:B6")), " ")
    X = Replace(Trim(X), " ", ",")
    Do Until InStr(1, X, ",,") = 0
        X = Replace(X, ",,", ",")
    Loop
    'lots of code between here and the next part'

    .Offset(1, 1) = X

Assumes you have no spaces in the range If there is post back and there is a way we can work around it.

How it works: Take the range (which is an array / group of values) and populate it straight to a string, joining each element with a space

Trim the string ie remove preceding and ending spaces

replace all remaining spaces with Commas

This is why we will have to put a couple more lines in if your data has spaces already, simple enough to do.

I favour this method as there is no looping involved.

Upvotes: 2

Related Questions