I am trying to remove empty cells from Array but the results are not I am expecting. The codes runs fine but repeat some values from row 11 to 14. Any help is highly appreciated.
VBA Code:
Dim a() As Variant
a = Data.Range("C2:C" & LR3).Value2
Dim j As Long, jj As Long: jj = 1
For j = 1 To UBound(a)
If a(j, 1) <> "" Then
a(jj, 1) = a(j, 1)
jj = jj + 1
End If
Next j
Data.Range("E2").Resize(UBound(a)).Value2 = a
AAPL LEAP OPTION TEST_REVA.xlsm | |||||
---|---|---|---|---|---|
C | D | E | |||
1 | |||||
2 | 200 | ||||
3 | 100 | ||||
4 | 200 | 23.12 | |||
5 | 100 | -46.24 | |||
6 | 233.74 | ||||
7 | 4.12 | ||||
8 | |||||
9 | |||||
10 | |||||
11 | 23.12 | 23.12 | |||
12 | -46.24 | -46.24 | |||
13 | 233.74 | 233.74 | |||
14 | 4.12 | 4.12 | |||
15 | |||||
16 | |||||
17 | |||||
18 | |||||
Sheet1 |