excel vba null value in array

Question

my code is

          Sub sch()
          Dim wb1 As Excel.Workbook
          Dim wb2 As Excel.Workbook
          Dim wb3 As Excel.Workbook
          Dim arr1() As Variant
          Dim arr2() As Variant
          Dim arr3() As Variant
          Set wb1 = Workbooks("Contractor Manpower Tracking_NE_02.06.2015.xlsx")
          arr1 = wb1.Sheets("Sheet2").Range("D3:D89").Value
          Workbooks("k.xlsm").Activate

          For Row = 1 To UBound(arr1, 1)
               For col = 1 To UBound(arr1, 2)
                    If Not arr1(Row, col) = " " Then
                         Sheets("Sheet1").Cells(Row + 1, 1) = arr1(Row, col)
                    End If
              Next col
           Next Row
         End Sub

I want to eliminate null values in cells but the some of the cells are remaining blank.Please help

Answer 1

Usually you get NULL values (very specific type) when you extract from databases

One way to deal with those NULL values in Excel is like this

If IsNull(arr1(Row, col)) Then arr1(Row, col) = vbNullString

other than that, both suggestions in comments are good:

If Not Len(trim(arr1(Row, col))) = 0 Then ...

Merged cells can cause you unexpected issues: for example you'll have to determine how a value from 3 vertical cells merged in Sheet2 will map to one cell in Sheet1 (most likely you'll want the same value populated in 3 cells in Sheet1)