Macro to take text from multiple columns and eliminate blanks

[adamheon]

I use a =CONCATENATE formula in columns R,Z,AH,AP,AX,BF & BN to combine 2 cells in adjacent columns.

I'm looking to run a macro that will take the data from the columns above and put them to a separate sheet and remove the rows that are blank.

I attached a simple example of what I describe above.

[alansidman]

Which column in your real life workbook will indicate the last row to be copied. In your current example, we have no way of knowing. That will change the line of code for lr. You will have to change the range column.

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

[adamheon]

Hi Alpha Frog, that works but what I can't have it do is combines text that's in the same row. For instance in Row4 there is text in column R & AH. When the code runs it combines those 2 cells into 1.

alansidman, it would stop at row 698.

[alansidman]

I have changed the last row in the code above to 698. Is this a fixed number. Will it change?

[adamheon]

No the row # will not change.

When I run that macro the first column (R) fills in correctly. However all the other columns are not. see image below

Capture.PNG

[alansidman]

Ok. In cell N6 on Sheet 2, Place this term "Control"

Here is amended code:

Please Login or Register  to view this content.

v	N	O	P	Q	R	S	T	U
6	Control	COLUMN R DATA	COLUMN Z DATA	COLUMN AH DATA	COLUMN AP DATA	COLUMN AX DATA	COLUMN BF DATA	COLUMN BN DATA
7	1	ABAS		ABAS
8	2		ABAS
9	3	ABAS				SDAS
10	4			4-AS
11	5							ABAS
12	6		ABAS		ABAS
13	7	ABAS
14	8	ABAS
15	9				TRAS
16	10		36+AS
17	11					SFAS
18	12				GFAS
19	13		SDAS
20	14			ABAS
21	15				ABAS
22	16							ABAS

[AlphaFrog]

Hi Alpha Frog, that works but what I can't have it do is combines text that's in the same row. For instance in Row4 there is text in column R & AH. When the code runs it combines those 2 cells into 1.

I thought that's what you wanted.

PERFERABLY ALL TEXT IN THE 8 COLUMNS ON SHEET1 WOULD BE COMBINED INTO 1 COLUMN HERE

Can you better explain and give some examples?

[adamheon]

AlphaFrog, sorry for the confusion. I'll try to explain better.

Each cell that has text within the 8 columns on Sheet1 would be moved to 1 single column on Sheet2 with no spaces. I reattached the example with the Sheet2 data manually filled in as i think it should come in.

From this:

2.PNG

To this:

1.PNG

[AlphaFrog]

AlphaFrog, sorry for the confusion. I'll try to explain better.

Each cell that has text within the 8 columns on Sheet1 would be moved to 1 single column on Sheet2 with no spaces. I reattached the example with the Sheet2 data manually filled in as i think it should come in.

No problem. Try this...

Please Login or Register  to view this content.

[adamheon]

worked perfect! thank you both!