Trying to eliminate blank cells in a list.

[RumJellybean]

Hello all,
I am using the following VBA function to eliminate blanks from a list.
User-Defined function NoBlanks- Command(NoBlanks)--Removes all blanks from a range<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Option Base 1
Function NoBlanks(ParamArray rgs()) As Variant
Dim v() As Variant
Application.Volatile True
For i = LBound(rgs) To UBound(rgs)
For j = 1 To Application.CountA(rgs(i))
num = num + 1
ReDim Preserve v(num)
v(num) = rgs(i)(j)
Next
Next
NoBlanks = Application.Transpose(v)
End Function

My function is "=noblanks(Sheet1!J9:J40,Sheet2!J9:J40)" which returns a list of data from those two ranges.

It works great with data that is manually entered. However even though it also “works” with Data that has been returned via the lookup or concatenate functions, I am having trouble getting the following function to work correctly with it:

=IF(OR(D9="SHT",D9="PL"),CONCATENATE(IF(C9>0,C9&" ",""),IF(D9>0,D9&" ",""),IF(I9>0,I9&" "&"GA"&" ",""),IF(E9>0,E9&"''"&" ",""),IF(F9>0,F9&" ",""),IF(G9>0,G9&"''","")),CONCATENATE(IF(C9>0,C9&" ",""),IF(D9>0,D9&" ",""),IF(E9>0,E9&" ",""),IF(F9>0,F9&" ",""),IF(G9>0,G9&" ",""),IF(H9>0,H9&" ",""),IF(I9>0,I9&" ","")))
<o></o>
In this situation, it returns all of the data AND blanks instead of skipping the blank cells. I am almost certain that even though the cell is blank, there is still a value of some sort that the “noblanks” function detects. I realize the “IF” formula above could probably be simplified as well, but I’m not sure how to do it.
Related post with example here.

 

[jbeaucaire]

My guess is that you would have to:

1) Adjust your Concatenation formulas to NOT put in those " " spaces when the values are blank, or
2) Trick your function into using a TRIM() function or SUBSTITUTE() to remove all the spaces and see if there's anything left to determine if the cell is actually "blank".

 

[jbeaucaire]

Looking at your concatenation formula, it appears you are doing some sort of STRING CONCATENATION. If this is the case, there may be a cleaner solution with a much simpler final formula.

Use ExcelJeanie
http://www.excel-jeanie-html.de/html/hlp_start_en.php

...to post up a visual example of the data and the resulting concatenated string, if you don't mind.

 

[RumJellybean]

Basically it takes the data entered and concatenate changes it around for a summary sheet that i can print out. The reason i did this is so it will be more understandable when i show it to other people. I dont work with excel for a living, and i am self taught, so there are alot of ways to do things that i have no clue of. Any suggestions would be greatly appreciated.
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 178px"><COL style="WIDTH: 56px"><COL style="WIDTH: 39px"><COL style="WIDTH: 19px"><COL style="WIDTH: 43px"><COL style="WIDTH: 19px"><COL style="WIDTH: 40px"><COL style="WIDTH: 216px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">Carbon HRS </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Stencil; TEXT-ALIGN: center">SHT</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-STYLE: italic; FONT-FAMILY: Arial; TEXT-ALIGN: center">48</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">X</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-STYLE: italic; FONT-FAMILY: Arial; TEXT-ALIGN: center">48</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">X</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-STYLE: italic; FONT-FAMILY: Arial; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Carbon HRS SHT 11 GA 48'' X 48''</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Carbon HRS </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Stencil; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">C</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center">@</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">5.4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">Carbon HRS C 4 @ 5.4 </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F9</TD><TD>=IF(D9="","",VLOOKUP(D9,DIVIDERS,2))</TD></TR><TR><TD>H9</TD><TD>=IF(D9="","",VLOOKUP(D9,DIVIDERS,3))</TD></TR><TR><TD>J9</TD><TD>=IF(OR(D9="SHT",D9="PL"),CONCATENATE(IF(C9>0,C9&" ",""),IF(D9>0,D9&" ",""),IF(I9>0,I9&" "&"GA"&" ",""),IF(E9>0,E9&"''"&" ",""),IF(F9>0,F9&" ",""),IF(G9>0,G9&"''","")),CONCATENATE(IF(C9>0,C9&" ",""),IF(D9>0,D9&" ",""),IF(E9>0,E9&" ",""),IF(F9>0,F9&" ",""),IF(G9>0,G9&" ",""),IF(H9>0,H9&" ",""),IF(I9>0,I9&" ","")))</TD></TR><TR><TD>F10</TD><TD>=IF(D10="","",VLOOKUP(D10,DIVIDERS,2))</TD></TR><TR><TD>H10</TD><TD>=IF(D10="","",VLOOKUP(D10,DIVIDERS,3))</TD></TR><TR><TD>J10</TD><TD>=IF(OR(D10="SHT",D10="PL"),CONCATENATE(IF(C10>0,C10&" ",""),IF(D10>0,D10&" ",""),IF(I10>0,I10&" "&"GA"&" ",""),IF(E10>0,E10&"''"&" ",""),IF(F10>0,F10&" ",""),IF(G10>0,G10&"''","")),CONCATENATE(IF(C10>0,C10&" ",""),IF(D10>0,D10&" ",""),IF(E10>0,E10&" ",""),IF(F10>0,F10&" ",""),IF(G10>0,G10&" ",""),IF(H10>0,H10&" ",""),IF(I10>0,I10&" ","")))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[jbeaucaire]

Alrightythen...first lets add a STRING CONCATENATION UDF to your worksheet:

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
    
    For Each Cell In CellBlock
        If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & " "     'normally ", "
    Next
    
    ConCatRange = Left(sbuf, Len(sbuf) - 1)         'normally (sbuf) -2)
End Function

I made a couple of adjustments to this string concatenation tool. It works like this:

=ConcatRange(A1:A10)

...and would normally gather every non-empty cell into a single comma-delimited string. For your uses, I took out the comma so it gathers all into a space delimited format.

You could play with the line marked in red to provide more validations if you wish. Anyway, put that UDF into a normal module and save your sheet.

The next thing I need to suggest is you setup your other formulas to include the quote in the cell string when it is needed, like 48" instead of just 48.

Anyway, once that's in place, you can use your new UDF like so:

Excel Workbook
	C	D	E	F	G	H	I	J
9	Carbon HRS	SHT	48"	X	48"	X	11	Carbon HRS SHT 11 GA 48" X 48"
10	Carbon HRS	C	4	@	5.4			Carbon HRS C 4 @ 5.4
Sheet1

Does this help you? Is this something you can work with or adapt?

 

[RumJellybean]

Thats an awesome function! It returns the #VALUE! error though. I've almost decided that I need to change a few thing around for simpilicity, but I'm sure I will be able to adapt it. By the way, is there an actual way to make or fuction to make zeros, " ", or "" as a true blank?

 

[jbeaucaire]

Experiment with a sample list until you get it working right. My sample above is based on your data and I made it work, so you just need to find what's triggering the value error. Use the formula evaluator to see if you can spot it.

Blank means blank. Empty. It's not the same thing as "has no visible answer". If you put a formula in a cell, it's not blank, it's just returning null or it's returning zero and you have "[ ] Zero values" turned off in the TOOLS > OPTIONS > VIEW settings for that sheet.

 

[RumJellybean]

Thats an awesome function! It returns the #VALUE! error though. I've almost decided that I need to change a few thing around for simpilicity, but I'm sure I will be able to adapt it. By the way, is there an actual way to make or fuction to make zeros, " ", or "" as a true blank?

I'm sorry I must have been in a hurry when I responded the last time. I hope I didn't sound rude The function works fine! What I meant to say was it returns a #value! error when the source cells are blank. I was just trying to find a way to get a blank if nothing visible was there. They have me doing so many different things at my workplace, it's hard to keep track of everything.