Remove Blanks from Array 2

[johnny45]

Hello,
I'm using the following code to get data from a user form...I would like to remove the blanks or spaces from the created array...The data comes from a multy line text box, so I must first remove the CR...but I'm not sure how to remove any blanks or spaces that might be in the array

    Dim txt As String
    Dim x As Variant
    Dim i As Long
    txt = Replace(upctxt, Chr(13), " ")
    x = VBA.split(txt, " ")
    For i = 0 To UBound(x)
       MsgBox x(i)
    Next i

 

[mintjulep]

What is a blank?

Any and all spaces?

Why are you putting spaces in the string in the first place?

Take a look at Trim()

 

[johnny45]

The array is from a multy line text box
if there is a CR at the end of the text the VBA.Split creates a blank in the array. I was not sure if it would be simpler to clean the array or test the array values as I process it...

 

[PHV]

What about something like this ?

x = Split(Replace(upctxt, Chr(13), " "), " ")
For i = 0 To UBound(x)
  MsgBox "'" & Trim(x(i)) & "'"
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnny45]

Thanks...but the spaces or blanks are not leading or trailing.....

using the SPLIT with " ", so it delimites based on spaces.

EX:
this

is
a
test

the array the split will return is
this
blank
is
a
test
blank

How could I remove the blank's from the array which th esplit functions generates ?

 

[PHV]

What about this ?

txt = Trim(Replace(upctxt, Chr(13), " "))
While InStr(txt, "  ")
  txt = Replace(txt, "  ", " ")
WEnd
x = Split(txt, " ")
For i = 0 To UBound(x)
  MsgBox "'" & x(i) & "'"
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnny45]

Thanks PHV, but I still end up with blanks

Is there not a way to loop the array and remove the blanks that way ?

 

[PHV]

but I still end up with blanks
What is the value of txt before the split ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnny45]

EX:
this
carrige return
is
a
test
carrige return

 

[PHV]

My question was:
What is the value of txt before the split and after the WEnd?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnny45]

befor VBA.Split:
this
is

a
test

after WEnd:
this
is

a
text

 

[PHV]

I'd replace this:
txt = Trim(Replace(upctxt, Chr(13), " "))
with this:
txt = Trim(Replace(Replace(upctxt, Chr(10), " "), Chr(13), " "))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnny45]

Sorry Still getting Blanks/space in array

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I would do what is already suggested, but if you need to clean an array after the fact.

Public Sub Test()
  Dim varArray(0 To 6) As Variant
  Dim theCleanArray() As Variant
  Dim intCount As Integer
  
  varArray(0) = "This"
  varArray(1) = " "
  varArray(2) = "Is"
  varArray(3) = Null
  varArray(4) = "A"
  varArray(5) = "       "
  varArray(6) = "Test"
  
  Debug.Print "old Array:"
  For intCount = LBound(varArray) To UBound(varArray)
    Debug.Print varArray(intCount)
  Next intCount
  theCleanArray = cleanArray(varArray)
  Debug.Print vbCrLf & "clean Array:"
  For intCount = LBound(theCleanArray) To UBound(theCleanArray)
    Debug.Print theCleanArray(intCount)
  Next intCount
 
End Sub

Public Function cleanArray(varArray() As Variant) As Variant()
  Dim tempArray() As Variant
  Dim oldIndex As Integer
  Dim newIndex As Integer
  ReDim tempArray(LBound(varArray) To UBound(varArray))
  For oldIndex = LBound(varArray) To UBound(varArray)
   If Not Trim(varArray(oldIndex) & " ") = "" Then
     tempArray(newIndex) = varArray(oldIndex)
     newIndex = newIndex + 1
   End If
 Next oldIndex
 ReDim Preserve tempArray(LBound(varArray) To newIndex - 1)
 cleanArray = tempArray
End Function

results

old Array:
This
 
Is
Null
A
       
Test

clean Array:
This
Is
A
Test

 

[johnny45]

Sub ttt()
mupc.Show
'MsgBox upctxt
If upctxt = "" Then Exit Sub
Debug.Print upctxt
txt = Trim(Replace(Replace(upctxt, Chr(10), " "), Chr(13), " "))
'While InStr(txt, "  ")
' txt = Trim(Replace(Replace(upctxt, Chr(10), " "), Chr(13), " "))
'Wend
Debug.Print txt

x = VBA.split(txt, " ")
For i = 0 To UBound(x)
  MsgBox x(i)
Next i
End Sub

txt = Trim(Replace(Replace(upctxt, Chr(10), " "), Chr(13), " ")) seems to do the same as txt = Trim(Replace(upctxt, vbCrLf, " ")).....

I realy think the only way to do this is to loop the array and remove blanks....the trim resolves the trailing space ...but if there is a blank line between 2 lines with data, it gets converted to a double space, so the split then returns a single space as part of the array

 

[PHV]

Why have you commented out the While loop ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnny45]

It was looping with NO end...

 

[PHV]

My suggested code was:

Sub ttt()
mupc.Show
'MsgBox upctxt
If upctxt = "" Then Exit Sub
Debug.Print upctxt
txt = Trim(Replace(Replace(upctxt, Chr(10), " "), Chr(13), " "))
While InStr(txt, "  ")
  txt = Replace(txt, "  ", " ")
Wend
Debug.Print txt
x = VBA.split(txt, " ")
For i = 0 To UBound(x)
  MsgBox x(i)
Next i
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnny45]

Thank You PHV WORKED GREAT

and Thank you MajP for you alternative