Delete or exclude blank cells in an array

  • Thread starter Thread starter stakar
  • Start date Start date
S

stakar

Is there a way to delete blank cells in an array or not to include the
when am i creating the array??
When i create the unique list of values i get the blank cells too.

Thats a part of the code
-------------------------------------------------------
'Create a unique list of 2nd column
On Error Resume Next
For lngLoop = 1 To UBound(aryIn)
colUnique.Add Item:=CStr(aryIn(lngLoop, 2))
key:=CStr(aryIn(lngLoop, 2))
Next
On Error GoTo 0

'How many unique values
lngRows = colUnique.Count

'Number the unique values and write column's data in sheet2
For lngLoop = 1 To lngRows
strValue = colUnique.Item(1)
colUnique.Remove (strValue)
colUnique.Add Item:=lngLoop, key:=strValue
wks_3.Cells(3 + lngLoop, 1).NumberFormat = "@"
wks_3.Cells(3 + lngLoop, 1).Value = strValue
Next
 
I usually do this job (in code) in an Excel worksheet. You can then do
sort (getting rid of blanks) and check consecutive numbers for a match
 
Since you are looping, just check it the value to be added is blank, if so,
don't add it.
 
Back
Top