Find if an array has blanks.

[farmerscott]

Hi Everybody,

1. Just wondering if anyone had a good way to check if there was any blanks in an array? It has to cover numbers and words. I would like to check it first and then 'do something', if possible.

2. Does anyone know why the following COUNTBLANK is erroring when the AVERAGE is working fine?

Sub Add_average_data()
  
 
 Dim x As Long
 Dim y As Long
 Dim cell As Range
 Dim arr As Variant
 lr = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
 arr = Sheets(1).Range("B2:B" & lr)
 y = Application.WorksheetFunction.CountBlank(arr)
 x = WorksheetFunction.Average(arr)
 
 End Sub

thanks,

FarmerScott

 

[Rick Rothstein]

1) This logical test will return True if there is a blank element anywhere within your array and False otherwise

"|" & Join(Application.Transpose(arr), "|") & "|" Like "*||*"

or you could simply use the original range directly instead of the array...

WorksheetFunction.CountBlank(Range("B2:B" & LR))



2) The argument to the COUNTBLANK function is a Range only.

 

[farmerscott]

Hi Rick,

Thanks for the reply (and suggestions).

I think I will go with your-

WorksheetFunction.CountBlank(Range("B2:B" & LR))

A couple of quick questions-

1. RE VBA coding: Is there anything out there that summaries which functions we have to put "worksheetfunction" in front of, those we can use alone (eg. LEFT, RIGHT) and those that only work on ranges and not arrays?

2. Can we assume that looping through an array is quicker than a range (with screen updating off)?


cheers

FarmerScott

 

[Rick Rothstein]

A couple of quick questions-

1. RE VBA coding: Is there anything out there that summaries which functions we have to put "worksheetfunction" in front of, those we can use alone (eg. LEFT, RIGHT) and those that only work on ranges and not arrays?

2. Can we assume that looping through an array is quicker than a range (with screen updating off)?

1) You should be able to see all of the functions house in the WorksheetFunction object by typing WorksheetFunction followed by a dot in any VB window. All the functions housed in the WorksheetFunction object are duplicates of Excel worksheet functions and must be prefixed with the WorksheetFunction reference (you can also reference the Application object in place of the WorksheetFunction)... VBA itself also has a slew of functions as well (which do not require any prefixed object reference).

2) Yes, in general, that is a true statement.

 

[farmerscott]

Hi Rick,

thanks for your comments.

I went back and had a look at the intellisense that comes up when you put the above mentioned functions (COUNTBLANK and AVERAGE). COUNTBLANK does mention that the the argument (arg1) does need to be a range where as, AVERAGE just asks for arg1, arg2, etc. I guess it comes down the looking closely and taking these clues from the intellisense a bit more literal.

As a matter of curiosity and learning can you explain your line of code-

"|" & Join(Application.Transpose(arr), "|") & "|" Like "*||*"

Is there a way you can put this over a couple of lines to see how this works.

cheers,

FarmerScott

 

[Rick Rothstein]

As a matter of curiosity and learning can you explain your line of code-

"|" & Join(Application.Transpose(arr), "|") & "|" Like "*||*"

When you apply Application.Transpose (also could be written WorksheetFunction.Transpose) to a two dimensional array consisting of a single column of values (this is what assigning a vertical range of cells to a Variant variable which I had done earlier in my code), it returns a normal one-dimensional VBA array. I don't know why it does that... all I know is that it does in fact do that. Now, the reason I needed a normal one-dimensional VBA array is that is the only kind of array the the Join function will work on. The Join function takes every element of the array passed into it and makes a text string where each of those elements is separated by the specified delimiter. In the code above, assume the arr variable was assigned these values that were located in a vertical range of cells...

A
B
C
D

Then Join(Application.Transpose(arr), "|") would produce this text string...

A|B|C|D

Next I concatenate a pipe symbol onto each end of that text string to produce this...

|A|B|C|D|

I do this because the Like operator the code line works on patterns and I wanted the same pattern for each value (a pipe symbol, the value, another pipe symbol) even for the first or last value in the text string that the Like operator will be comparing. Okay, I showed you what the text string would look like when each cell was filled with a value, but suppose the original cells we constructed the array from had these values...

A
B

D

Notice the third cell is empty... nothing in it. If we follow the above outlined procedure, the text string that would be generated would be this...

|A|B||D|

Notice that double pipe symbol where the cell was empty... that is what we are going to use the Like operator to search for. The Like operator allows you to use wildcards, so we are going to use the asterisk with stands in for zero or more unspecified characters. So this is the pattern text string we will use in the Like operator to see if our generated text string has that pattern or not....

"*||*"

What this pattern string says is look for a spot in the generated text string where there is zero or more unspecified characters followed by a double pipe symbol followed by zero or more unspecified characters. Our generated text string...

|A|B||D|

meets that pattern so that the logical Like comparison would return True meaning there was a blank cell in the original range used to form the arr array. Hopefully that wordy explanation is somewhat clear.

 

[farmerscott]

Hi Rick,

quite amazing!!!

Any particular reason for using the "|" other than it might be the least used symbol, letter, number of the keyboard or is it a (or one of many) designated delimiter/s?

Would this still work if the blank cell had spaces (ie " ") but no text/numbers/data?

thanks again for your contribution.

Cheers,

FarmerScott

PS "The Don" looks like he has had a reasonable day in Singapore, how is the US media viewing it?

 

[Rick Rothstein]

Any particular reason for using the "|" other than it might be the least used symbol, letter, number of the keyboard....

That is the reason... any character that would never appear in the text could be used.

Would this still work if the blank cell had spaces (ie " ") but no text/numbers/data?

If a cell had a one or more spaces in it, then those spaces would appear between the "|" delimiters which would mean it would not be counted as being empty. I could add code to make a cell with one or more spaces be counted as if it were an empty cell if you actually need such functionality... just let me know.

 

[farmerscott]

Hi Rick,

Appreciate all your help.

FarmerScott