Macro to Move all data in Cols B:G in same Row as in Col A

[howard]

I would like a macro to move all data from Col B:G into same row as in Col A (those rows in Col A are blank where there is data in Cols B:G)


Your assistance is most appreciated

 

[Joe4]

I am afraid your question does not make much sense to me. I don't know what you are trying to do.
Perhaps if would make more sense if you could post an example (images, etc).

 

[howard]

Hi Joe

See link Below

The items in Col B:G to be moved to Col A (see highlighted items

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Joe4]

I do not have the ability to download files from my current location, so I cannot view your file right now.
If you could post images or samples to this thread using the "XL2BB" tool, I will be able to view it right away.
Otherwise, I will not get able to look at it until quite a bit later today (perhaps someone else may be able to download your file in the meantime).

Basically, we need to know what your data in columns B-G looks like, and how exactly do you want to combine it all into one column.
Is it a simple concatenation, cramming things together, or is there a space or some other delimiter between each value, i.e.
maybe the following will work.

For row 2, in cell A2, use the formula:
=CONCATENATE(B2,C2,D2,E2,F2,G2)

 

[howard]

Thanks Joe

See Sample data using XL2BB tool

macro to Move Items from Col B to G to same Row Col A.xlsm
	A	B	C	D	E	F	G
14	BR1 LIMITED STATEMENT OF FINANCIAL POSITION
15
16			Notes	2020 R	2019 R	2018 R	2017 R
17		ASSETS
18		Non-current assets
19		Property, plant and equipment Taxation	2 3	75058 96340 153398	70910 90504 160410	50915 80416 160519	30213 60505 151320
Data Import

 

[Joe4]

That is the "before" image.
What should this look like, after everything has been combined in column A?

 

[howard]

Hi Joe See final Results below of where the datamust be moved to

macro to Move Items from Col B to G to same Row Col A Final.xlsm
	A	B	C	D	E	F
17		Notes	2020 R	2019 R	2018 R	2017 R
18	ASSETS
19	Non-current assets
20	Property, plant and equipment Taxation	2 3	75058 96340 153398	70910 90504 160410	50915 80416 160519	30213 60505 151320
Data Import

 

[Joe4]

Oh, I think I see what you are talking about now.
Do you mean that if column A is blank in any row, you want to shift the values from B:G over to the left one column?

If so, there are a few ways you can go about it:

Manually
1. Highlight column A
2. Press the F5 key
3. Click the "Special" button
4. Select the "Blanks" radio button
5. Hit "OK"
6. Right-click on any blank cell
7. Select "Delete"
8. Make sure the "Shift cells left" radio button is selected
9. Click "OK"

That should do it.

If you want to do it via VBA code, just turn on your Macro Recorder as you record the steps above.

 

[howard]

You I have interpretd it correctly -if column A is blank in any row, I want to shift the values from B:G over to the left one column

Thanks for setting out the procedure

 

[Joe4]

You are welcome.