extracting blanks from an array

osubuckeye2007

New Member
Joined
Mar 19, 2013
Messages
9
I have an array that is made up of inputs from a UserForm

Code:
[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:

Code:
[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:

Code:
[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

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

either solution should work
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Try this, it should only populate FieldWidths with non-blanks.
Code:
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

New Member
Joined
Mar 19, 2013
Messages
9
thanks!

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

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
Try this, it should only populate FieldWidths with non-blanks.
Code:
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

New Member
Joined
Mar 19, 2013
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top