How to make array copy ignoring blank values?

dmadhup

Board Regular
Joined
Feb 21, 2018
Messages
146
Office Version
  1. 365
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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:

Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,417
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
JK
1Hdr1Hdr2
2Data102Data102
3Data103Data103
4Data106
5Data108
6Data106Data109
7Data1010
8Data108Data1011
9Data109Data1012
10Data1010Data1016
11Data1011Data1017
12Data1012Data1018
13Data1019
14Data1021
15Data1022
16Data1016Data1023
17Data1017Data1024
18Data1018Data1025
19Data1019
20
21Data1021
22Data1022
23Data1023
24Data1024
25Data1025
Sheet2



Code:
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

Board Regular
Joined
Feb 21, 2018
Messages
146
Office Version
  1. 365
Thank you JoeMo and DanteAmor for the response! I appreciate for helping me.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows
Code:
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...
Code:
[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]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,343
Messages
5,635,742
Members
416,876
Latest member
Sokali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top