VBA to take a row and convert it into a list that uses two columns at a time,

[tonywatsonhelp]

Hi Everyone,
hope someone can help

I have a range K3:CD3

now The first Column is Position and the second is Name (Going from K3 across),
I want a macro that in its memory can take this info and create a list of positions and names
So if K3 = SALES
L3 = Tony
I want SALES: Tony
then a new line then M3 and N3 etc.
so I get a list,
I need this stored in VBA so I can insert it into an email
so let's say I need this sorted as "Nlist"

however, sometimes a position has a name missing, so as it creates the list I need it to say if the name is "" skip

please help if you can I've been working on this all day and only have until tomorrow to get it done
Thanks
Tony

 

[JamesCanale]

This is a bit silly:

365: You can make a VBA command:
Nlist = range("K5")

And put this into your spreadsheet:

MrExcelPlayground18.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
3	SALES	Tony	ADMIN	Fred	BOSS		THING	Mary	STUFF	John	WHAT	Jamie	WHO	Bill
4
5	SALES: Tony ADMIN: Fred THING: Mary STUFF: John WHAT: Jamie WHO: Bill
Sheet14

Cell Formulas
Range	Formula
K5	K5	=LET(a,WRAPROWS(K3:CD3,2),b,TAKE(a,,1),c,TAKE(a,,-1),d,b&": "&c,e,FILTER(d,c<>""),f,TEXTJOIN(CHAR(10),TRUE,e),f)

 

[tonywatsonhelp]

Thank you but i need t vba not a formula,can't explain why but please trust me

 

[Peter_SSs]

create a list

What, exactly do you mean by a "list"? That is, what form should it take?

Is it anything like this?

Sub Test()
  Dim NList As Variant
  
  NList = Evaluate("LET(a,WRAPROWS(K3:CD3,2),FILTER(a,TAKE(a,,-1)<>""""))")
End Sub

For the same data that @JamesCanale posted the result is an array with the relevant values

 

[jolivanes]

Quick and crude. Check before taking as gospel.

Sub Maybe()
Dim i As Long, x As Long, j As Long, lst1, lst2, a As String, b As String
x = 1
j = 1
lst1 = Application.Transpose(Sheets("Sheet1").Range("K3:CD3").Value)
ReDim lst2(1 To UBound(lst1) / 2)
    For i = LBound(lst1) To UBound(lst1) / 2
        If lst1(j, 1) = "" Then a = "skip" Else a = lst1(j, 1)
        If lst1(j + 1, 1) = "" Then b = "skip" Else b = lst1(j + 1, 1)
            lst2(x) = a & ": " & b
        a = "": b = ""
        x = x + 1
        j = j + 2
    Next i
Cells(1, 1).Resize(UBound(lst2)) = Application.Transpose(lst2)    '<---- Change Cell reference as required.
End Sub

 

[Peter_SSs]

Re-reading your question & noting this ..

I need this stored in VBA so I can insert it into an email

.. perhaps you want this extension to my previous suggestion which incorporates all the results into a single string with line breaks between each pair.

Sub Test_v2()
  Dim NList As String
  
  NList = Evaluate("LET(a,WRAPROWS(K3:CD3,2),SUBSTITUTE(TEXTJOIN({"": "",""#""},,FILTER(a,TAKE(a,,-1)<>"""")),""#"",CHAR(10)))")
  MsgBox NList
End Sub

The MsgBox is only included so I can show the result string as follows

 

[tonywatsonhelp]

Peter this is perfect thank you so much
jolivanes, your also work so thank you for another way to do it

 

[tonywatsonhelp]

Hi Peter (Or anyone else that can help,

I realize i can use this code elsewhere,
could it be edited so that instead of just working on Row 3 it works on rows 3 to 13?

thank you very much
Tony

 

[Peter_SSs]

could it be edited so that instead of just working on Row 3 it works on rows 3 to 13?

It is not clear what the expected outcome(s) of that is(are).
Some sample data and expected result(s)?

 

[tonywatsonhelp]

Hi Peter,
So I want it to do exactly what it does now for range K3:CD3,
but then repeat the process on range K4:CD4 etc up to K13:CD13
so I would imagine it would be something like

For i = 3 to 13
then your code:
NList = Evaluate("LET(a,WRAPROWS(K & i :CD & i ,2),SUBSTITUTE(TEXTJOIN({"": "",""#""},,FILTER(a,TAKE(a,,-1)<>"""")),""#"",CHAR(10)))")
  MsgBox NList
next i

but i can't quite get it to work