How to make array copy ignoring blank values?

[dmadhup]

Hello,

I am seeking help in copying one array values to another one but only the nonblank values.

For example:

For aa = 2 To 9 Step 1
quantityArray = Worksheets("CSM").Range("J" & ii & ":J" & kk).Value
Next aa

There may be several blank values in quantityArray. So, How to only copy the non-blank values to another array called quantity2Array.

Thank you!

 

[DanteAmor]

With this line you are not copying values, you are only putting the value of a cell in the variable quantityArray.

quantityArray = Worksheets("CSM").Range("J" & ii & ":J" & kk).Value

I also do not understand what means ii or kk in your macro I do not see the values ​​that variables ii or kk take.


To exclude values ​​equal to blank, you can put something like this:

For aa = 2 To 9 Step 1
if cells(aa, "J").value <> "" then
    'here your code to copy
    quantityArray = Worksheets("CSM").Range("J" & ii & ":J" & kk).Value
end if
Next aa

 

[JoeMo]

Does this help? Arr1 holds all the values in J2:J25, some of which are blanks. Arr2 holds only the non-blank values and is written to K2:K18.

Excel Workbook
	J	K
1	Hdr1	Hdr2
2	Data102	Data102
3	Data103	Data103
4		Data106
5		Data108
6	Data106	Data109
7		Data1010
8	Data108	Data1011
9	Data109	Data1012
10	Data1010	Data1016
11	Data1011	Data1017
12	Data1012	Data1018
13		Data1019
14		Data1021
15		Data1022
16	Data1016	Data1023
17	Data1017	Data1024
18	Data1018	Data1025
19	Data1019
20
21	Data1021
22	Data1022
23	Data1023
24	Data1024
25	Data1025
Sheet2

Sub dmadhup()
Dim Arr1, Arr2
'Create a 2-D array with some blank values
Arr1 = Range("J2:J25").Value
'Create a second 2-D array to exclude blanks
ReDim Arr2(1 To UBound(Arr1, 1), 1 To 1)
For i = LBound(Arr1, 1) To UBound(Arr1, 1)
    If Arr1(i, 1) <> "" Then
        ct = ct + 1
        Arr2(ct, 1) = Arr1(i, 1)
    End If
Next i
Range("K2:K" & ct + 1).Value = Arr2
End Sub

 

[dmadhup]

Thank you JoeMo and DanteAmor for the response! I appreciate for helping me.

 

[Rick Rothstein]

Sub dmadhup()
Dim Arr1, Arr2
'Create a 2-D array with some blank values
Arr1 = Range("J2:J25").Value
'Create a second 2-D array to exclude blanks
ReDim Arr2(1 To UBound(Arr1, 1), 1 To 1)
For i = LBound(Arr1, 1) To UBound(Arr1, 1)
    If Arr1(i, 1) <> "" Then
        ct = ct + 1
        Arr2(ct, 1) = Arr1(i, 1)
    End If
Next i
Range("K2:K" & ct + 1).Value = Arr2
End Sub

Since the values in Column J are "data" (that is, not formulas), then your macro can be written as a one-liner...

[table="width: 500"]
[tr]
	[td]Sub dmadhup()
  Range("J2", Cells(Rows.Count, "J").End(xlUp)).SpecialCells(xlConstants).Copy Range("K2")
End Sub[/td]
[/tr]
[/table]