Remove empty cell from array

[adnan1975]

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.

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

 

[Fluff]

How about

Dim a As Variant, b As Variant

a = Data.Range("C2:C" & LR3).Value2
ReDim b(1 To UBound(a), 1 To 1)
Dim j As Long, jj As Long: jj = 1
For j = 1 To UBound(a)
If a(j, 1) <> "" Then
b(jj, 1) = a(j, 1)
jj = jj + 1
End If
Next j

 Data.Range("E2").Resize(jj).Value2 = b

 

[DanteAmor]

With loop

Sub removeempty()
  Dim i As Long, j As Long
  j = 2
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value <> "" Then
      Range("E" & j).Value = Range("C" & i).Value
      j = j + 1
    End If
  Next
End Sub

Without loop

Sub Macro2()
  Range("C2", Range("C" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, 23).Copy Range("E2")
End Sub

and with the array

Sub macro3()
  Dim a As Variant
  Dim i As Long, j As Long
  Dim data As Worksheet
  
  Set data = ActiveSheet
  a = data.Range("C2", Range("C" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) <> "" Then
      j = j + 1
      b(j, 1) = a(i, 1)
    End If
  Next
  data.Range("E2").Resize(j).Value = b
End Sub

 

[adnan1975]

How about

Dim a As Variant, b As Variant

a = Data.Range("C2:C" & LR3).Value2
ReDim b(1 To UBound(a), 1 To 1)
Dim j As Long, jj As Long: jj = 1
For j = 1 To UBound(a)
If a(j, 1) <> "" Then
b(jj, 1) = a(j, 1)
jj = jj + 1
End If
Next j

 Data.Range("E2").Resize(jj).Value2 = b

Thank you so much it worked well.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[adnan1975]

With loop

Sub removeempty()
  Dim i As Long, j As Long
  j = 2
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value <> "" Then
      Range("E" & j).Value = Range("C" & i).Value
      j = j + 1
    End If
  Next
End Sub

Without loop

Sub Macro2()
  Range("C2", Range("C" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, 23).Copy Range("E2")
End Sub

and with the array

Sub macro3()
  Dim a As Variant
  Dim i As Long, j As Long
 
  a = Range("C2", Range("C" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) <> "" Then
      j = j + 1
      b(j, 1) = a(i, 1)
    End If
  Next
  Range("E2").Resize(j).Value = b
End Sub

Thank you Dante. it works too but I prefer arrays since my data sets are large and looping through each cell takes longer. Once again thank you for great help as always.