Shifting Columns

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
129
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: 6
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,518
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
 
Upvote 0
Solution

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
129
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: 2
Upvote 0

Fluff

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

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
129
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: 4
Upvote 0

Fluff

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

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
129
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
 
Upvote 0

Fluff

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

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
129
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: 1
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,518
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
 
Upvote 0

Forum statistics

Threads
1,186,371
Messages
5,957,463
Members
438,306
Latest member
Crystal_Blue

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