RumJellybean
New Member
- Joined
- Apr 20, 2009
- Messages
- 16
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.
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.