We help IT Professionals succeed at work.

VBA script that will move any blue colored heading text to the left 1 column

I have a lot of Excel catalog pricing documents and I need to swap Column (A) with column (B) but the Blue Header text that was originally in Column A needs to stay in Column A, so I need a Macro VBA script that will swap Column A and B but move Blue Header Text back to Column A.

Here is a really short video showing what I need.
https://www.loom.com/share/18fbd577a81647cc81d36ea422b20168
Comment
Watch Question

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:

Hi Aaron,


You should have also attached a sample file to work with.

Anyways, please try the following code and let me know if that works as desired.

Considering the setup, I don't think that the header color is important while shifting the headers.


Sub RearrangeData()
Dim lr As Long
Dim Rng As Range

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 2).End(xlUp).Row

Range("B:B").Cut
Range("A1").Insert

For Each Rng In Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 2).Areas
    Rng.Cells(1).Offset(-1, 1).Cut Rng.Cells(1).Offset(-1, 0)
Next Rng

Application.ScreenUpdating = True
End Sub
Aaron RoesslerWeb Developer

Author

Commented:
That worked perfectly but it missed the first Row.  Here is an example file to work with.
Compatible-Filters-and-Membranes.xls
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:

Okay, test the attached and let me know if that works as desired now.

Compatible-Filters-and-Membranes.xls

Aaron RoesslerWeb Developer

Author

Commented:
Subodh, thank you, this new script works great.

I also just realized after going through all my Excel files there is some inconsistency with my row heights. I have three different row heights throughout all my excel files.  See my attached image that shows these 3 row height examples.

Row Height #1. Main blue text headers all should be - row height (12)

Row Height #2. Part# and Description rows as well as the regular text rows all should be - row height (9.75)

Row Height #3. The empty rows in between the price breaks - row height (5) ..... This one may be not possible via VBA script?  but this is when the Main Blue Text header is the same for the parts underneath it but some parts have different price breaks so i need to separate with a skinny empty row.

I hope the two screenshots explain how I need all my Excel files to have similar 3 types of row heights.
Screen-Shot-2020-02-03-at-10.57.59-P.jpg
Screen-Shot-2020-02-03-at-11.09.08-P.jpg
Medias---Resin-copy.xls
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:

Please test the attached and see if the row heights are adjusted as per your requirement.

Medias---Resin-copy.xls

Aaron RoesslerWeb Developer

Author

Commented:
Hello again Subodh, FANTASTIC!  Your script is working great then for some reason it had errors with this specific file. I have attached the Original Excel file and then I ran your script and on rows 144, 169, 173, 177 and a few more.

I went through other files and only came across one other file so far that has this same issue. I am attaching that as well. "Valumax Fleck Valves" on row 101, 116 etc.
Fleck-Parts-and-Services-Original.xls
Fleck-Parts-and-Services---Rearragne.xls
Valumax-Fleck-Valves.xls
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:

Please find the attached with the three sheets.

Sheet called Original Data contains the data in original layout.

Sheet called Rearranged Data contains the data after the macro is run on this sheet.

Sheet called Test contains the original data and a button which you may click to test the tweaked code and see if the issue you reported is resolved.


Fleck-Parts-and-Services-Original.xls

Aaron RoesslerWeb Developer

Author

Commented:
Thanks again!! Subodh.  seems like it is working now.  I have another really simple VBA script I need but will start a new question since this script will do something different.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:

You're welcome Aaron! Glad it worked as desired.

I am glad I could help.

Thanks for the feedback!