Alter VBA for Cell Addressing in Excel 2013

[kyleno]

I found a VBA script to convert a column of text to a single cell separated by spaces (see below), BUT it will only select a list of data from column A and only return the data to column B.

I was able to figure out that altering the numbers in rows 7 and 8 (dataRow =# and listRow =#) will change the row numbers.

I would like to be able to specify a cell number for both the row/column that the list is located as well as the output cell for the new single cell list.

Any ideas?

Please and thank you.

~ Ky
----------------------------------
Sub generatecsv()

Dim dataRow As Integer
Dim listRow As Integer
Dim data As String

dataRow = 1: Rem the row that it is being read from column A otherwise known as 1 in vb script
listRow = 1: Rem the row in column B that is getting written

Do Until Cells(dataRow, 1).Value = "" And Cells(dataRow + 1, 1).Value = ""
If (data = "") Then
data = Cells(dataRow, 1).Value
Else
If Cells(dataRow, 1).Value <> "" Then
data = data & " " & " " & Cells(dataRow, 1).Value
Else
Cells(listRow, 2).Value = data
data = ""
listRow = listRow + 1
End If
End If
dataRow = dataRow + 1
Loop

Cells(listRow, 2).Value = data

End Sub

 

[Rick Rothstein]

Instead of giving us code you found and trying to indicate how you might want to modify it, why don't you just tell us exactly what you need and let us write the code to do exactly that? Just give us enough information for us to know what you know about what you want to do. Some ideas on that... what is your set up, how are you deciding where to start, how are you deciding where to stop, what exactly do you want done with the indicated data, etc., etc.?

 

[kyleno]

I have data in H7:H11 that I will be using to create a query in Access

like "*Test*"
or like "Best*"
or like "Rest*"
or like "*West*"
or like "Crest*".


I would like to be able to adjust this range as need


After running the script I would like the data to appear in cell B3

like "*Test*" or like "Best*" or like "Rest*" or like "*West*" or like "Crest*"


I would like to be able to adjust this specified cell as need

Thanks!

 

[Rick Rothstein]

I have data in H7:H11 that I will be using to create a query in Access

like "*Test*"
or like "Best*"
or like "Rest*"
or like "*West*"
or like "Crest*".


I would like to be able to adjust this range as need


After running the script I would like the data to appear in cell B3

like "*Test*" or like "Best*" or like "Rest*" or like "*West*" or like "Crest*"

I would like to be able to adjust this specified cell as need

The best way to handle this is using a UDF (user defined function). Here is the code (place it in a general module... same kind of module that macros go in)...

Function Combine(Rng As Range) As String
  Combine = Join(Application.Transpose(Rng))
End Function

To use it, just specify the range whose cells you want to combine (H7:H11 from your example) and place the formula in the cell you want that combined text to be in (B3 from your example). So, using your example, put this formula in cell B3...

=Combine(H7:H11)

 

[kyleno]

That worked great! Thanks!

 

[kyleno]

Is there a way to get this to do comma separated values too?

 

[Rick Rothstein]

Is there a way to get this to do comma separated values too?

Do you mean like this...

Function Combine(Rng As Range) As String
  Combine = Join(Application.Transpose(Rng)[B][COLOR="#FF0000"], ","[/COLOR][/B])
End Function

 

[kyleno]

Do you mean like this...

Function Combine(Rng As Range) As String
  Combine = Join(Application.Transpose(Rng)[B][COLOR="#FF0000"], ","[/COLOR][/B])
End Function

Yes! Thanks!

 

[kyleno]

Hey Rick,

I was wondering if there would be a way to alter this to exclude empty/blank cells? Thanks!

 

[Rick Rothstein]

I was wondering if there would be a way to alter this to exclude empty/blank cells?

Sure, but that takes a little more code to do...

Function Combine(Rng As Range) As String
  Dim vNum As Variant
  Combine = Join(Application.Transpose(Rng), ",")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    Combine = Replace(Combine, String(vNum, ","), ",")
  Next
  If Right(Combine, 1) = "," Then Combine = Left(Combine, Len(Combine) - 1)
End Function

Note: I could do this as a one-liner, but the above is more efficient.