Concatenate cell range and skip empty cells in VBA

[Jo_ox]

Hi all

Hope someone can help me out a little bit

Here's some background info:

My excel file for reference:

https://www.dropbox.com/s/d2v4up2txuvm1fz/1 - Analyseresultatskema Farveplan VBA TEST 3.xlsm?dl=0

The sheet "Analyseresultatskema" is used to visually evaluate test sample results.

Column G contains test sample ID and the following columns H:AH contain testresults for individual substances.

I have written a macro to color the cells in range (H:AH) green / yellow / red, based on criterias for each substance in another sheet "Grænseværdier til formatering" contained in the file.

With some help from a friendly forum member in here, I was also able to summarize a colorcode for the G-column.
(Thank you, LiveToExcel!)


Now, on to my problem:

What I'm having a bit of trouble with, is the column AI in the "Analyseresultater" sheet.
I'm using the column to concatenate results for all substances in a single cell roughly as folows:

Column AC	Column AD	Column AE	Column AF	Column AG	Column AH	Column AI
Substance 1	Substance 2	Substance 3	Substance 4	Substance 5	Substance 6	Concatenated result
10	20	30	0,5	Detected	Not Detected	Substance 1: 10 mg/kg Substance 2: 20 mg/kg Substance 3: 30 mg/kg Substance 5: Detected Substance 6: Not detected
500		10	1			Substance 1: 500 mg/kg Substance 3: 10 mg/kg
				Not detected	Detected	Substance 5: Not detected Substance 6: Detected

<tbody>
</tbody>

So far I've been using a formula in excel - something along the lines of:

=CONCATENATE(IF(IS.EMPTY(AC2);"";"Substance 1: " & AC2 & " mg/kg" & CHR(10)); IF(IS.EMPTY(AD2) .... and so on.


As you can see, the formula skips blank cells and I manually define a range of columns (which substances) that I want to be concatenated by going through them 1 by 1, since I basically only want certain substances to be concatenated.

The columns (substances) that I want to be concatenated are a static range. For instance, I'm skipping columns S:Z in my full concatenate formula in the sheet.

The above approach works well to a certain point, but the problem is that due to a big number og substances in the sheet, the number of arguments in the cell starts to exceed the limit of possible arguments in a cell (I assume).

So after a certain number of arguments (concatenated substances), excel stops showing the concatenated string and just displays "######" in the cell.

So my question is: Can this concatenation be done in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> to avoid the argument limitations in a cell?

Any help or pointers in the right direction would be greatly apreciated!

 

[Rick Rothstein]

Re: Concatinate cell range and skip empty cells in VBA

Put this formula in cell AI2 and copy it down...

=MID(IF(AC2="","",CHAR(10)&AC$1&": "&AC2&" mg/kg")&IF(AD2="","",CHAR(10)&AD$1&": "&AD2&" mg/kg")&IF(AE2="","",CHAR(10)&AE$1&": "&AE2&" mg/kg")&IF(AG2="","",CHAR(10)&AG$1&": "&AG2)&IF(AH2="","",CHAR(10)&AH$1&": "&AH2),2,999)

 

[Jo_ox]

Re: Concatinate cell range and skip empty cells in VBA

Hi Rick

Since I have a Danish localized version of Excel, I had to do a little bit of formula translation to get it to work

In general, the formula is working just as well as "=Concatenate" did, but...

The problem is that your example only spans 5 cells in range AC:AH.

My end formula has to concatenate at least 15 cells in range H:AH

Here's my expansion of your formula above (with danish syntax, but it should be straight forward):

=MIDT(
HVIS(H2="";"";"Benzo(a)pyren: "&H2&" mg/kg")
&HVIS(I2="";"";TEGN(10)&"Dibenso(a,h)antracen: "&I2&" mg/kg")
&HVIS(J2="";"";TEGN(10)&"Sum PAH: "&J2&" mg/kg")
&HVIS(K2="";"";TEGN(10)&"Bly: "&K2&" mg/kg")
&HVIS(L2="";"";TEGN(10)&"Cadmium: "&L2&" mg/kg")
&HVIS(M2="";"";TEGN(10)&"Chrom: "&M2&" mg/kg")
&HVIS(N2="";"";TEGN(10)&"Kobber: "&N2&" mg/kg")
&HVIS(O2="";"";TEGN(10)&"Nikkel: "&O2&" mg/kg")
&HVIS(P2="";"";TEGN(10)&"Zink: "&P2&" mg/kg")
&HVIS(Q2="";"";TEGN(10)&"Arsen: "&Q2&" mg/kg")
&HVIS(R2="";"";TEGN(10)&"Kviksølv: "&R2&" mg/kg")
&HVIS(AA2="";"";TEGN(10)&"PCB: "&AA2&" mg/kg")
&HVIS(AC2="";"";TEGN(10)&"KP Indikation: "&K2)
&HVIS(AE2="";"";TEGN(10)&"Klorparaffiner: "&AE2&" %")
&HVIS(AH2="";"";TEGN(10)&"Asbest: "&AH2);2;999)

That just seems to be too many arguments for excel to handle in one cell, so the output is shown as "########"

 

[Rick Rothstein]

Re: Concatinate cell range and skip empty cells in VBA

Hi Rick

Since I have a Danish localized version of Excel, I had to do a little bit of formula translation to get it to work

In general, the formula is working just as well as "=Concatenate" did, but...

The problem is that your example only spans 5 cells in range AC:AH.

My end formula has to concatenate at least 15 cells in range H:AH

Your original post did not hint at the fact that you will have more columns than your example showed, so I designed my solution for what you showed us (it is pretty much always a bad idea to simplify your questions for us). For that many columns, I think a macro would be a better approach. With that said, give this one a try...

[table="width: 500"]
[tr]
	[td]Sub Substances()
  Dim R As Long, LastRow As Long, LastCol As String, Txt As String, vNum As Variant
  LastRow = Cells.Find("", , xlValues, , xlRows, xlPrevious).Row - 1
  LastCol = Split(Cells(1, Columns.Count).End(xlToLeft).Address, "$")(1)
  For R = 2 To LastRow
    Txt = Join(Application.Index(Evaluate(Replace(Replace("IF(AC#:@#="""","""",AC1:@1)", "#", R), "@", LastCol)), 1, 0), vbLf)
    For Each vNum In Array(121, 13, 5, 3, 3, 2)
      Txt = Replace(Txt, String(vNum, vbLf), vbLf)
    Next
    If Right(Txt, 1) = vbLf Then Txt = Left(Txt, Len(Txt) - 1)
    With Cells(R, Columns(LastCol).Column + 1)
      .Value = Txt
      .WrapText = True
    End With
    Rows(R).AutoFit
  Next
End Sub[/td]
[/tr]
[/table]

 

[Jo_ox]

Re: Concatinate cell range and skip empty cells in VBA

Hi Rick

I couldn't quite get your script to work, but in the mean time, I got the abovementioned formula to work instead. The problem apparently had to do with cell formatting.
After another attempt at googling for the problem solution some people were reporting that changing the formatting from "Text" to "General / Standard" made the cells display the full content.

My column was seemingly formatted as "General", so just to try something I changed the formatting to "Text" and suddenly the cells that displayed #### started showing the concatenated string... Changing the formatting of the cells back to "General" still left the cells showing the concatenated text...

I'm not sure what was wrong with my formatting originally, but it seems to work now!

Thanks for taking the time to try and help me out though!