VBA Select Range and Move based on conditions

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
44
Hi All,

I am looking for some help on VBA to Select Range and Move based on conditions.

I would like to move all the contents between "Project A" and "Project B" one column to the right. Same with all contents between Project B and end of table which will also move 1 column to the right.

This is how the table looks now:

Column A Column B Column C
Project A
1/1/2019 Jack 5
2/1/2019 Ryan 8
7/1/2019 James 10
Project B
1/1/2019 Jack 5
2/1/2019 Ryan 8
7/1/2019 James 10
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,155
Office Version
  1. 365
Platform
  1. Windows
Try this. Assumes code begins in A1.

Code:
Sub MOVEIT()
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(, 1)
    .FormulaR1C1 = "=IF(ISERROR(FIND(""Project"",RC[-1])),RC[-1],"""")"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants).Offset(, -1).ClearContents
End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,948
Office Version
  1. 365
Platform
  1. Windows
Another option
Code:
Sub Giggs1991()
    Range("A:A").SpecialCells(xlConstants, xlNumbers).Insert xlToRight
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,948
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,130,281
Messages
5,641,293
Members
417,202
Latest member
AndyVBA

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