Shifting Columns

Andresleo47

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
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
No, you would need
VBA Code:
      Range(Fnd, Cells(Rows.Count, Fnd.Column)).Resize(, 2).Insert xlToRight
 
Upvote 0
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
What is the code you are using?
 
Upvote 0
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
You need to change the variable in the Range( line as well.
 
Upvote 0
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: 2
Upvote 0
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,212,938
Messages
6,110,784
Members
448,297
Latest member
carmadgar

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
Back
Top