extracting blanks from an array

[osubuckeye2007]

I have an array that is made up of inputs from a UserForm

[B]FieldWidths = Array(InputForm.TextBox1.Value, InputForm.TextBox2.Value, InputForm.TextBox3.Value, ...)[/B]

there are 18 boxes but sometimes we only need to use the first few. I'd like to leave the unused ones blank, but this causes the array to look like so:

[B]Fieldwidths = (10,24,8,15,,,,,,,...)[/B]

and my code will not work with the empty values
our current solution has been to place "0" in the unused boxes, so that the array looks like so:

[B]Fieldwidths = (10,24,8,15,0,0,0,0,0,0...)[/B]

But this causes some other undesired effects.

is there a way that I could take that array and eliminate the blanks? they are always at the end.


otherwise, is there a way to make the use of this array just stop at the last blank? currently the array is being used with this code

[B]For i = 0 To UBound(FieldWidths)
[/B]

either solution should work

 

[Norie]

Try this, it should only populate FieldWidths with non-blanks.

For I = 1 To 18

     If InputForm.Controls("TextBox" & I).Value<>"" Then
           Redim Preserve FieldWidths(I-1) 
           Preserve FieldWidths(I-1) = InputForm.Controls("TextBox" & I).Value
     Else
           Exit For
     End If
Next I

 

[osubuckeye2007]

thanks!

I did not know about the redim preserve function. I tweaked it a bit and it works!

For i = 1 To 18     

     If InputForm.Controls("TextBox" & i).Value = "" Then
           ReDim Preserve FieldWidths(i - 1)
           Exit For
     End If
Next i

 

[Rick Rothstein]

Try this, it should only populate FieldWidths with non-blanks.

For I = 1 To 18

     If InputForm.Controls("TextBox" & I).Value<>"" Then
           Redim Preserve FieldWidths(I-1) 
           Preserve FieldWidths(I-1) = InputForm.Controls("TextBox" & I).Value
     Else
           Exit For
     End If
Next I

Possible Problem Alert For osubuckeye2007
-------------------------------------------------------------------
Is it possible for intermediate textboxes to be blank? In other words, could your field widths array possibly look like this...

(10,24,,,8,,15,,,,...)

If so, then I am pretty sure Norie's proposal would align the data in the textboxes to the wrong field positions.

 

[osubuckeye2007]

thanks for the heads up. The user should enter 0 for any intermediate fields that they do not require. But the blanks are always at the end.
So I think our codes are okay, though I don't have a lot of experience.
I was able to shorten Norie's proposal to just cut the array and stop working when it found the first blank.

But your warning does remind me to add the usage of "0" in intermediate fields