Shifting cells with Example for clarification-in a Macro

ottokyleneidert

New Member
Joined
Aug 21, 2014
Messages
7
ABCDEFGHIJKLMNOPQRST
1180.191.810520.8Pounds41890Hydrogen0.1Grain41890Total0.09Grain41890Wobbe1357Calc41890Comments
2641.10.41017Calc1337Comments
3460.351.710581.01Pounds41890Hydrogen0.01Grain41890Wobbe1359Calc41890Comments

<tbody>
</tbody>

Hopefully I can articulate this well. In a macro I use, .Column("X:X") .Delete Shift:x1ToLeft is used, but I've come to a point where the code keeps doing that and will wipe out the middle row due to how it was originally written. These three rows are an example, I think the easiest way would be to shift the cells out to where their counterparts are.

How could I shift cell F2 to R2? and P3 to T3? What do you guys think? And blank cells are fine.

Thank you in advance, -Otto Neidert
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
ottokyleneidert,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


If I understand you correctly, if any row contains Calc, them move Calc, and, the data to the right, in the same row, to column R = 18.

Sample raw data (columns B, C, and, D are hidden so that the data will fit in the MrExcel display area):


Excel 2007
AFGHIJKLMNOPQRSTU
118Pounds41890Hydrogen0.1Grain41890Total0.09Grain41890Wobbe1357Calc41890Comments
264Calc1337Comments
346Pounds41890Hydrogen0.01Grain41890Wobbe1359Calc41890Comments
4
Sheet1


After the macro:


Excel 2007
AEFGHIJKLMNOPQRSTU
1180.8Pounds41890Hydrogen0.1Grain41890Total0.09Grain41890Wobbe1357Calc41890Comments
264Calc1337Comments
3461.01Pounds41890Hydrogen0.01Grain41890Wobbe1359Calc41890Comments
4
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub MoveCalc_to_col_R()
' hiker95, 09/13/2014, ME805139
Dim c As Range, ca As Range, lc As Long, n As Long, myc
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  Set ca = Rows(c.Row).Find("Calc", LookAt:=xlWhole)
  If Not ca Is Nothing Then
    If ca.Column <> 18 Then
      lc = Cells(c.Row, Columns.Count).End(xlToLeft).Column
      n = lc - ca.Column + 1
      myc = Range(Cells(ca.Row, ca.Column), Cells(ca.Row, lc)).Value
      Range(Cells(ca.Row, ca.Column), Cells(ca.Row, lc)).ClearContents
      Cells(ca.Row, 18).Resize(, n).Value = myc
      Set ca = Nothing
      Erase myc
    End If
  End If
Next c
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MoveCalc_to_col_R macro.
 
Last edited:
Upvote 0
Thank you for your insight hiker95, your code would work, and maybe I will have to use it-if there is no other way. Here is some clarification:
Code:
.Columns("A:B").Delete Shift:=xlToLeft.Columns("B:D").Delete Shift:=xlToLeft
.Columns("C:E").Delete Shift:=xlToLeft
.Columns("D:F").Delete Shift:=xlToLeft
.Columns("E:G").Delete Shift:=xlToLeft
'.Range("O2").Offset(0, 4) = .Range("O2")
'.Range("P3").Offset(0, 4) = .Range("P3")
'.Columns("F:H").Delete Shift:=xlToLeft
'.Columns("G:I").Delete Shift:=xlToLeft
'.Columns("H:J").Delete Shift:=xlToLeft
'.Columns("H").Delete Shift:=xlToLeft
'.Columns("I").Delete Shift:=xlToLeft
.Columns("H").NumberFormat = "mm/dd/yy"
'.Range("G6") = .Range("E6")
.Range("E6").ClearContents

So '.Range("O2").Offset(0, 4) = .Range("O2") is something I tried to move a value over that I needed shifted over 4 just like the above example needing F2 to go to R2. The first few lines of code work, the commented stuff out is what I am still working on because if it goes that far-what I need is deleted. Any thoughts now?
 
Upvote 0
ottokyleneidert,

Thank you for your insight hiker95, your code would work, and maybe I will have to use it-if there is no other way.

Thanks for the feedback.

You are very welcome.

My macro code was written to solve your original request, your reply #1.


I do not have a clue as to what you just posted/requested?????

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0

Similar threads

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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