Macro button to collect data from 2 columns of unknown length & duplicates removal

Hi, I have two arrays (columns) of lengths j and k, where j, k <= 10,000. I would like to create a macro, which provides me with all unique values from both arrays, i.e. removes duplicates (and blanks).


Is the following process the best/fastest you can imagine?
1) Copy values from A1:A10000 into C1:C10000 and B1:B10000 into C10001:C20000*
2) Remove blanks from C1:C20000*
3) Remove duplicates from C1:C[latest nonblank row]
4) Sort C1:C[latest nonblank row] alphabetically
5) Copy C1:C[latest nonblank row] into D1: (as other functions are based on column D)
6) Delete/remove whole column C (i.e. column D with values will become C)


Would it be eventually possible to run steps 1-4 somewhere in the background, so that only "clean values" are being handled with?



*daffodil11's solution:

Sub RemoveBlanks()

For C = 1 To 2
For R = 1 To 10000
If Cells(R, C) <> "" Then Cells(R, C).Copy Range("C" & lastrow + 1)
lastrow = Range("C" & Rows.Count).End(xlUp).Row
Next
Next

End Sub

Hi,

VBA macros and certainly any which contain loops will always be slower than a macro that uses standard Excel functionality. The most efficient , quickest and elegant way is to create a single column list as you are doing and then use the Remove duplicates functionality. i.e.

Please Login or Register  to view this content.