Excel VBA create a list from non-blank cells in range

[jasonfish11]

Hi all,
I am trying to search for all non-blank cells in a range (let's say A1-A30) then with all non-blank values set a variable that is just a list of the cell values separated by a ";"

Essentially there will be email addresses in this list. Not all cells from A1-A30 will be populated, it's dependent on other circumstances to determine who should be getting the email (I've built this function out already). I'm just trying to wrap my head around how to search all non-empty cells and create a list of that separated by ";" so that I can then set that list as a variable.

Then with my coding for the email I could just call that variable in the .To section.

Any help on the best way to go about doing this would be greatly appreciated.

 

[Fluff]

Are A1:A30 hard values, or formulae?

 

[jasonfish11]

They are hard values that are being created by some of my other VBA coding.

 

[Fluff]

In that case try

Sub jasonfish11()
   Dim Strg As String
   With Range("A1:A30")
      Strg = Replace(Application.Trim(Join(Application.Transpose(.Value), " ")), " ", ";")
   End With
   MsgBox Strg
End Sub

This assumes that the cells don't have any spaces in them.

 

[jasonfish11]

In that case try

Sub jasonfish11()
   Dim Strg As String
   With Range("A1:A30")
      Strg = Replace(Application.Trim(Join(Application.Transpose(.Value), " ")), " ", ";")
   End With
   MsgBox Strg
End Sub

This assumes that the cells don't have any spaces in them.

Since they are email addresses they will not. Thank you so much this is much easier than the road I was heading down and struggling with.

 

[Fluff]

You're welcome & thanks for the feedback