VBA: Concatenate headers with data in rows below

[kohlo]

Hello everyone!

I'm bulk converting a bunch of product content from a spreadsheet into basic HTML and I'm looking for a VBA code that lets me concatenate the product's technical specs including the header each time, but only if the cell isn't empty.
The content will be displayed in lists using <ul> tags, so each spec has to be surrounded by <li> and </li>

Something that works similar to the code from this post https://www.mrexcel.com/forum/excel...der-if-cells-not-blank-range.html#post4579089 would be excellent, except this doesn't concatenate the headers -with- the content below.

So a simplifed example of my spreadsheet looks something like this:


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Spec 1:[/TD]
[TD]Spec 2:[/TD]
[TD]Spec 3:[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD]Girls[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50 cm[/TD]
[TD]Boys[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]100 cm[/TD]
[TD]Uni[/TD]
[/TR]
</tbody>[/TABLE]








The amount of rows and columns with specs is variable, but it's not a problem for me to edit the code a little bit to point it to the relevant rows and columns if needed.

What I would like for the code to generate for me in the rows next to the data is:

<li>Spec 1: Red</li> <li>Spec 3: Girls</li>
<li>Spec 1: Blue</li>
<li>Spec 2: 50 cm</li> <li>Spec 3: Boys</li>
<li>Spec 1: Yellow</li> <li>Spec 2: 100 cm</li> <li>Spec 3: Uni</li>

The post I linked to above does about half of this, but sadly I'm not yet savvy enough with VBA to tweak it to use it in this way.

Any help would be greatly appreciated! Thanks so much!

 

[Peter_SSs]

Welcome to the MrExcel board!

See if something like this would suit you.

[PLAIN]Sub Specs()
  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long
  Dim s As String
  
  With Range("A1").CurrentRegion
    a = .Value
    uba2 = UBound(a, 2)
    For i = 2 To UBound(a)
      s = vbNullString
      For j = 1 To uba2
        If Len(a(i, j)) > 0 Then s = s & "<li>" & a(1, j) & " " & a(i, j) & "</li>"
      Next j
      a(i, 1) = s
    Next i
    With .Offset(, .Columns.Count).Resize(, 1)
      .Value = a
      .Cells(1).Value = "Specs"
      .Columns.AutoFit
    End With
  End With
End Sub[/PLAIN]

 

[Peter_SSs]

Results for me:

Book1
	A	B	C	D
1	Spec 1:	Spec 2:	Spec 3:	Specs
2	Red		Girls	Spec 1: RedSpec 3: Girls
3	Blue			Spec 1: Blue
4		50 cm	Boys	Spec 2: 50 cmSpec 3: Boys
5	Yellow	100 cm	Uni	Spec 1: YellowSpec 2: 100 cmSpec 3: Uni
Sheet1

 

[kohlo]

Thank you so much Peter_SSs, this does exactly what I asked for!

If it's not too much trouble, could you give any tips on how I can tweak this code if the specs data doesn't start in cell A1? I tried altering "With Range("A1").CurrentRegion" but that doesn't seem to change anything. No worries if it's too much of a hassle, I can paste the columns with specs in a new sheet temporarily to run the code on it and that works too.

 

[Peter_SSs]

Thank you so much Peter_SSs, this does exactly what I asked for!

Good news!

If it's not too much trouble, could you give any tips on how I can tweak this code if the specs data doesn't start in cell A1?

It shouldn't be too hard to adapt, but I would need to understand more about where they are.

1. Do they always start in a particular column? If so, what column?

2. Is there always just 3 columns or can there be more/less?

3. Do your headings all actually start with "Spec ..."?

4. Are the headings always in row 1?

 

[kohlo]

1. Do they always start in a particular column? If so, what column?

Nope, they might start in C or as far as Z depending on how much garbage the export gives me in the columns in front of the specs. It always starts in a different place, but they -are- always at the end of the columns.

2. Is there always just 3 columns or can there be more/less:

There can be more or less, it depends on the product category I'm working on. Basically it's always variable.

3. Do your headings all actually start with "Spec ..."?

The headings in my data are the actual attribute, so it could be size, colour, speed, power output, etc.

4. Are the headings always in row 1?

Yep!

It would be perfect if there could be a simple string in the code to indicate the range of columns I need that I can edit each time I run the code. Since the specs are always dumped in the last columns of the sheet, even just being able to indicate where the code should start concatenating would be a tremendous help.

 

[Peter_SSs]

Hmm, most of that doesn't look like a great deal of help. I don't suppose the cell immediately to the left of the first attribute heading is empty, or contains some recognisable (ie consistent) text?

Failing that, there are other options for the user to indicate the first column but for now I've implemented your idea of a simple change in the code to identify the first column.
The changes are all near the start of the code & highlighted below.

  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long, cols As Long, lr As Long
  Dim s As String
  
  Const FirstCol As String = "E"  '<- First attribute column
  
  cols = Cells(1, Columns.Count).End(xlToLeft).Column - Columns(FirstCol).Column + 1
  lr = Columns(FirstCol).Resize(, cols).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  With Range(FirstCol & 1).Resize(lr, cols)
    a = .Value
    uba2 = UBound(a, 2)

 

[kohlo]

Yes, that works perfectly, thank you so much! You've saved me from a million nested IF formulas! :D

 

[Peter_SSs]

Yes, that works perfectly, thank you so much! You've saved me from a million nested IF formulas! :D

Pleased to hear it.