Empty Cells, leave blank? RRS feed

  • Question

  • Good afternoon all.

    I have a macro, which I've modified, after receiving the basic model from Hans Vogelaar. 

    Public Sub CommandButton1_Click()

    Dim arr(1 To 7) As String
    Dim Col As Variant
    'Because Excel is limited to 2 criteria for filtering
    ' this code is set up to filter 7 criteria.
    ' The array elements which have no value assigned to them
    ' will show a blank cell. So, keep that in mind
    ' when seeing results.
     Col = RefEdit8.Value

        arr(1) = RefEdit1.Value
        arr(2) = RefEdit2.Value
        arr(3) = RefEdit3.Value
        arr(4) = RefEdit4.Value
        arr(5) = RefEdit5.Value
        arr(6) = RefEdit6.Value
        arr(7) = RefEdit7.Value

        Range("A1:N6438").AutoFilter Field:=Col, Criteria1:=arr, Operator:=xlFilterValues

    I am finding that not all my array elements are being filled when I use it, and would like to leave those outputs empty, when this happens. 

    What would I need, and where would I place it, to make the code leave the outputs without blank cells? 

    I tried an if test,

    if arr() = "=" then

    arr() = ""



    end if

    It does not work. It throws a mismatch error. 

    I tried changing the arr(1 to 7) as String to "as Variant" but it did not work either.

    The error shows up at the arr() = ...... 

    equal sign, after the arr()

    What would be the correct solution here? 



    Wednesday, September 2, 2015 11:20 PM


  • Ok.

    I apparently solved my desire. Or, perhaps it'd be more accurate to say that it's not throwing any errors. 

    In stepping through it, I see where it tests, and then moves on. 

    arr(_n) = RefEdit_n.Value

        If arr(_n) = "=" Then
                arr(_n) = ""
        End If

    Where "_n" is the given array number. 

    I.e., solved. 

    • Marked as answer by SteveDB1 Thursday, September 3, 2015 3:51 PM
    Thursday, September 3, 2015 3:51 PM