Deleting blank values from an array
 

I have an array that gets populated with dates from various ranges in my
spreadsheet. In the process blank values also get included. Once filled, I
would like to sort the array with the earliest date in H(0) and the blank
ones deleted by redimensioning the array.
I figured out one way to sort but the blank ones are all in the lower
indexes with the sorted dates at the end.
Is there an easy way to delete the empty elements of the array either while
sorting or another routine after the sort ?
I used the sorting routines in this help site

http://support.microsoft.com/?kbid=213818
I tried a filter routine but it created an error
Thanks

Deleting blank values from an array
 

Assume Column D having empty cells want to delete entire row.

Sub Macro2()
Columns("D:D").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

norika


--
norika
------------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
View this thread: http://www.excelforum.com/showthread...hreadid=375471

Deleting blank values from an array
 

It seems that the easiest approach would be to prevent blank values from
getting added to the array in the first place. Failing that, one simple
solution is to transfer non-blank values from the original array to a second
dynamic array. Then use the second array and forget the first. I'm sure
someone has a more elegant solution. I think it would be more efficient to
sort afterwards.

'Where Arr is array containing blanks and Arr2 is dynamic array
ii = 0
For i = LBound(Arr) To UBound(Arr)
If Arr(i) < "" Then
ReDim Preserve Arr2(ii)
Arr2(ii) = Arr(i)
ii = ii + 1
End If
Next


Regards,
Greg

"Rich J" wrote:

I have an array that gets populated with dates from various ranges in my
spreadsheet. In the process blank values also get included. Once filled, I
would like to sort the array with the earliest date in H(0) and the blank
ones deleted by redimensioning the array.
I figured out one way to sort but the blank ones are all in the lower
indexes with the sorted dates at the end.
Is there an easy way to delete the empty elements of the array either while
sorting or another routine after the sort ?
I used the sorting routines in this help site

http://support.microsoft.com/?kbid=213818
I tried a filter routine but it created an error
Thanks

Deleting blank values from an array
 

Thanks norika for the response. I can't delete the rows in the spreadsheet
though.

Thanks Greg. Your routine will work. There will not be more than 80
elements in the array so it will be quick running. I will also try to
eliminate the blank entries before they are added to the array. So may be a
combination of both to work every time.

Any other ideas would be appreciated too. Always nice to have multiple
solutions.

"Greg Wilson" wrote:

It seems that the easiest approach would be to prevent blank values from
getting added to the array in the first place. Failing that, one simple
solution is to transfer non-blank values from the original array to a second
dynamic array. Then use the second array and forget the first. I'm sure
someone has a more elegant solution. I think it would be more efficient to
sort afterwards.

'Where Arr is array containing blanks and Arr2 is dynamic array
ii = 0
For i = LBound(Arr) To UBound(Arr)
If Arr(i) < "" Then
ReDim Preserve Arr2(ii)
Arr2(ii) = Arr(i)
ii = ii + 1
End If
Next


Regards,
Greg

"Rich J" wrote:

I have an array that gets populated with dates from various ranges in my
spreadsheet. In the process blank values also get included. Once filled, I
would like to sort the array with the earliest date in H(0) and the blank
ones deleted by redimensioning the array.
I figured out one way to sort but the blank ones are all in the lower
indexes with the sorted dates at the end.
Is there an easy way to delete the empty elements of the array either while
sorting or another routine after the sort ?
I used the sorting routines in this help site

http://support.microsoft.com/?kbid=213818
I tried a filter routine but it created an error
Thanks

Deleting blank values from an array
 

Rich J wrote:
I have an array that gets populated with dates from various ranges in my
spreadsheet. In the process blank values also get included. Once filled, I
would like to sort the array with the earliest date in H(0) and the blank
ones deleted by redimensioning the array.
I figured out one way to sort but the blank ones are all in the lower
indexes with the sorted dates at the end.
Is there an easy way to delete the empty elements of the array either while
sorting or another routine after the sort ?
I used the sorting routines in this help site

http://support.microsoft.com/?kbid=213818
I tried a filter routine but it created an error
Thanks

If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook

H = SubArray(H, ArrayCountIf(H, 0), UBound(H), 1, 1)

Alan Beban

Deleting blank values from an array
 

I tried a filter routine but it created an error

If you had trouble with the Filter command on a 1-Dim array, you may not
have set it up correctly.
See if there are any ideas here that may help.
Filter won't work with blanks, so I hope this gets fixed in a future version
of Excel.

Sub Demo()
Dim v As Variant
ReDim v(1 To 10)

Const Flag As String = "xxx"
Dim j As Long

'// Small 1-D array
For j = 1 To 10
v(j) = j
Next j

'// Make two elements blank!
v(3) = vbNullString
v(7) = vbNullString

'// Scan for blanks
For j = 1 To 10
If v(j) = vbNullString Then v(j) = Flag
Next j

v = Filter(v, Flag, False)
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Rich J" wrote in message
...
I have an array that gets populated with dates from various ranges in my
spreadsheet. In the process blank values also get included. Once filled,
I
would like to sort the array with the earliest date in H(0) and the blank
ones deleted by redimensioning the array.
I figured out one way to sort but the blank ones are all in the lower
indexes with the sorted dates at the end.
Is there an easy way to delete the empty elements of the array either
while
sorting or another routine after the sort ?
I used the sorting routines in this help site

http://support.microsoft.com/?kbid=213818
I tried a filter routine but it created an error
Thanks

Deleting blank values from an array
 

Thanks Dana, that looks like another good solution. I had taken Greg's code
and ran with it. A few name changes and a correction in my code and it works
great.

"Dana DeLouis" wrote:

I tried a filter routine but it created an error

If you had trouble with the Filter command on a 1-Dim array, you may not
have set it up correctly.
See if there are any ideas here that may help.
Filter won't work with blanks, so I hope this gets fixed in a future version
of Excel.

Sub Demo()
Dim v As Variant
ReDim v(1 To 10)

Const Flag As String = "xxx"
Dim j As Long

'// Small 1-D array
For j = 1 To 10
v(j) = j
Next j

'// Make two elements blank!
v(3) = vbNullString
v(7) = vbNullString

'// Scan for blanks
For j = 1 To 10
If v(j) = vbNullString Then v(j) = Flag
Next j

v = Filter(v, Flag, False)
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Rich J" wrote in message
...
I have an array that gets populated with dates from various ranges in my
spreadsheet. In the process blank values also get included. Once filled,
I
would like to sort the array with the earliest date in H(0) and the blank
ones deleted by redimensioning the array.
I figured out one way to sort but the blank ones are all in the lower
indexes with the sorted dates at the end.
Is there an easy way to delete the empty elements of the array either
while
sorting or another routine after the sort ?
I used the sorting routines in this help site

http://support.microsoft.com/?kbid=213818
I tried a filter routine but it created an error
Thanks