Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    47

    Unanswered: Cleaning up array.

    Hi,

    I tried to clean up an array, using the following code. The redim preserve statement doesn't work.

    Can someone help out?

    Code:
    Public Sub CleanArray()
    
    Dim x, y As Variant
    Dim i, c As Integer
    
    'Dump all values in a range to an array
    y = Sheets("Sheet1").Range("A1:A175")
    x = y
    
    Erase x    'reset the new array elements
    
    'Weed out the blanks in the array
    i = 1  'counter for old array
    c = 0  'counter for new array
    
    While i < 175
        If Not IsEmpty(y(i, 1)) Then
           c = c + 1
           ReDim Preserve x(c, 1) 'This one doesn't work
           x(c, 1) = y(i, 1)
        End If
        i = i + 1
    
    Wend
    
    End Sub
    Thanx

  2. #2
    Join Date
    Feb 2004
    Posts
    47
    Got the problem!!

    redim preserve doesnt work for first element of a two-dimensional array. It only works for the second element.

    got to make it work as follows:

    Code:
    ReDim Preserve x(1, c)  'Instead of x(c, 1)
    The array gets transposed though....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •