VBa to take concurrent columns and consolidate into one long one

M

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66
Hi All,

Again, I'm sure this will be about a 2 second solution to one of you geniuses...

This is the scenario.. I have, starting in Column B, anywhere from 1-20 following rows with values in them. I want this VBa to combine them all into one single column, preferably in maybe Column Z so that I can just copy it from there..

Example of the content below, and then after it would be done processing.. Hopefully it formats alright.. Extra love if you make it so that it removes duplicates either while or after..


Before-
[TABLE="width: 1537"]
<colgroup><col><col span="12"></colgroup><tbody>[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0231-8848518-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]0212-0409936-0551[/TD]
[TD]0052-2762147-0551[/TD]
[TD]0007-3087282-0551[/TD]
[TD]0049-3293161-0551[/TD]
[TD]0245-3310888-0551[/TD]
[TD]0253-4742318-0551[/TD]
[TD]0063-5312341-0551[/TD]
[TD]0094-5479015-0551[/TD]
[TD]0037-7109407-0551[/TD]
[TD]0212-7828750-0551[/TD]
[TD]0003-9589269-0551[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orders[/TD]
[TD]SPH[/TD]
[TD]0212-0409936-0553[/TD]
[TD]0052-2762147-0553[/TD]
[TD]0007-3087282-0553[/TD]
[TD]0049-3293161-0553[/TD]
[TD]0245-3310888-0553[/TD]
[TD]0037-4543683-0553[/TD]
[TD]0063-5312341-0553[/TD]
[TD]0094-5479015-0553[/TD]
[TD]0037-7109407-0553[/TD]
[TD]0212-7828750-0553[/TD]
[TD]0231-8848518-0553[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

After
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl79, width: 64"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]SPH[/TD]
[/TR]
[TR]
[TD="class: xl79"]SPH[/TD]
[/TR]
[TR]
[TD="class: xl79"]SPH[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-0409936-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0052-2762147-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0007-3087282-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0049-3293161-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0245-3310888-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-4543683-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-4543683-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-4543683-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0253-4742318-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0063-5312341-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0094-5479015-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0003-9589269-0551[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0037-7109407-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0212-7828750-0553[/TD]
[/TR]
[TR]
[TD="class: xl79"]0231-8848518-0553[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
You want all the values in column B to ?? copied into column Z.
Is that what you want?

Which column is column ??

From column A to what column??
Can I guess "A" to "Y"
 
Upvote 0
My Aswer Is This said:
You want all the values in column B to ?? copied into column Z.
Is that what you want?

Which column is column ??

From column A to what column??
Can I guess "A" to "Y"
Click to expand...

Sorry poorly explained- Yes basically take all content of rows B-Y and paste into one long vertical column in Z!
 
Upvote 0
Try this:
Code: 
Sub Test()
Application.ScreenUpdating = False
Dim i As Long
    For i = 2 To 25
    Range(Cells(1, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Copy Cells(Cells(Rows.Count, "Z").End(xlUp).Row + 1, 26)
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Let me know if you are interested in a non-VBA solution. It is not ideal. It is a 2 formula process. The first formula prints out all those numbers in a row. The next formula removes duplicates.

Mike Szczesny
 
Upvote 0
Here's another VBA solution, which will remove the duplicate values.
Code: 
Sub Combine_removedupe()

    Dim dict As Object
    Dim Cl As Range
    Dim UsdRws As Long
    
    Set dict = CreateObject("scripting.dictionary")
    UsdRws = Cells.Find("*", after:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    With dict
        For Each Cl In Range("B1:Y" & UsdRws)
            If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
        Next Cl
        Range("Z1").Resize(.Count) = Application.Transpose(.keys)
    End With

End Sub
 
Upvote 0
With my original simple script this added line of code will remove duplicates:
Code: 
Sub Test()
'Modified 9-1-18 6:21 PM EDT
Application.ScreenUpdating = False
Dim i As Long
    For i = 2 To 25
    Range(Cells(1, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Copy Cells(Cells(Rows.Count, "Z").End(xlUp).Row + 1, 26)
    Next
Range("Z1:Z" & Cells(Rows.Count, "Z").End(xlUp).Row).RemoveDuplicates 1, xlNo
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formula or UDF to split a cell value into rows within a new cell
Replies
6
Views
916
Rick Rothstein
Rick Rothstein
N
  • Question Question
Data consolidation between multiple sheets into one
Replies
1
Views
713
severynm
severynm
B
  • Question Question
VBA that combine the texts in two cells, then search in a spesific folder and if it exists return "Yes" if not "No"
Replies
5
Views
338
burchette
B
Geeta2014
  • Question Question
Vba to 1) Add and 2) Delete rows based on Criteria entered in input box that displays on screen
Replies
3
Views
551
igold
igold
S
  • Question Question
multiple tables to 1 list with flexible rows
Replies
2
Views
551
sukin
S

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

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
Back
Top