Shifting Columns

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
100
Hi Dear Community,

I'm trying to find the best way to Shift (Cut - Paste) all the information below and to the right of the "Worker" cell in VBA. Basically, I would like all the information below and to the right of cell E2 to be cut and pasted in Cell D2. It will delete all the information from cell D2 below, and that's fine!

However I would not like to use specific cells (Because it may vary with time). In this case it's cell E2 below and to the right, but in the future this might change. So instead of using the cell E2 as a parameter, I would like to use the word "Worker" in row 2 as a Parameter.

Am I making sense? Kindly advise, If it's not clear, I would be happy to rephrase it.

Thanks,

Andres
 

Attachments

  • Capture22.PNG
    Capture22.PNG
    31.1 KB · Views: 4
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Andresleo()
   Dim Fnd As Range
   Set Fnd = Range("2:2").Find("Worker", , , xlWhole, , xlNext, False, , False)
   If Not Fnd Is Nothing Then
      Range(Fnd.Offset(, -1), Cells(Rows.Count, Fnd.Offset(, -1).Column)).Delete xlToLeft
   End If
End Sub
 
Solution

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
100
Wow, that works...As a result, the Worker Status column (An everything else) got shifted to the left, which is correct. However, if I want to do the same thing with the "Work Order Status Column", but this time Cutting and pasting everything to the right, I'm assuming the procedure would be as follows?

Sub Andresleo()
Dim Fnd As Range
Set Fnd = Range("2:2").Find("Work Order Status", , , xlWhole, , xlNext, False, , False)
If Not Fnd Is Nothing Then
Range(Fnd.Offset(, 2), Cells(Rows.Count, Fnd.Offset(, 2).Column)).Delete xlToRight
End If
End Sub
 

Attachments

  • Capture23.PNG
    Capture23.PNG
    19.2 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
No, you would need
VBA Code:
      Range(Fnd, Cells(Rows.Count, Fnd.Column)).Resize(, 2).Insert xlToRight
 

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
100

ADVERTISEMENT

Interesting, kindly find attached what the code did. Now my "Worker" column is misplaced again lol.

Did I miss anything?

Thanks,
 

Attachments

  • Capture24.PNG
    Capture24.PNG
    14.7 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
What is the code you are using?
 

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
100

ADVERTISEMENT

It would be this one:

Sub Andresleo47()
Dim Fnd2 As Range
Set Fnd2 = Range("2:2").Find("Work Order Status", , , xlWhole, , xlNext, False, , False)
If Not Fnd2 Is Nothing Then
Range(Fnd, Cells(Rows.Count, Fnd.Column)).Resize(, 2).Insert xlToRight
End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
You need to change the variable in the Range( line as well.
 

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
100
True, I changed the variable, but it's doing the same thing... Look at the result in the attached. This is the whole code I'm using:

Sub ShiftColumns()

Dim Fnd As Range
Set Fnd = Range("2:2").Find("Worker", , , xlWhole, , xlNext, False, , False)
If Not Fnd Is Nothing Then
Range(Fnd.Offset(, -1), Cells(Rows.Count, Fnd.Offset(, -1).Column)).Delete xlToLeft
End If


Dim Fnd2 As Range
Set Fnd2 = Range("2:2").Find("Work Order Status", , , xlWhole, , xlNext, False, , False)
If Not Fnd2 Is Nothing Then
Range(Fnd2, Cells(Rows.Count, Fnd.Column)).Resize(, 2).Insert xlToRight
End If

End Sub
 

Attachments

  • Capture25.PNG
    Capture25.PNG
    24.9 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
You are still using Fnd on this line
VBA Code:
Range(Fnd2, Cells(Rows.Count, Fnd.Column)).Resize(, 2).Insert xlToRight
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,958
Messages
5,767,321
Members
425,404
Latest member
Bairkus

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