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

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,868
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
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).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,868
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks Joe

See Sample data using XL2BB tool

macro to Move Items from Col B to G to same Row Col A.xlsm
ABCDEFG
14 BR1 LIMITED STATEMENT OF FINANCIAL POSITION
15
16Notes2020 R2019 R2018 R2017 R
17ASSETS
18Non-current assets
19Property, plant and equipment Taxation2 375058 96340 15339870910 90504 16041050915 80416 16051930213 60505 151320
Data Import
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
That is the "before" image.
What should this look like, after everything has been combined in column A?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,868
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEF
17Notes2020 R2019 R2018 R2017 R
18ASSETS
19Non-current assets
20Property, plant and equipment Taxation2 375058 96340 15339870910 90504 16041050915 80416 16051930213 60505 151320
Data Import
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,868
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,521
Messages
5,659,303
Members
418,496
Latest member
WHYCHumphrey

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