Shifting Data Right Within A Range
I needed a macro to shift data within a range incrementally to the left, one column at a time. The code below was provided by Wouter HM, for which I am extremely grateful.
=========================================
Sub ShiftLeftInRange()
Dim intCol As Long
Dim celLoop As Range
'
'determen the last used column number
intCol = Range("testRange").Columns.Count + Range("testrange").Column - 1
For Each celLoop In Range("testRange")
If celLoop.Column < intCol Then
' Shift cells witnin range 1 column to the left
celLoop.Value = celLoop.Offset(0, 1).Value
Else
' clear cel in last used column
celLoop.ClearContents
End If
Next
End Sub
=========================================
The above code works great. BUT...
1. How do I also get these same values to shift to the right? And
2. After selecting either a €śright shift€ť or €śleft shift€ť of data, how can I loop through a list of desired ranges (about 10 in total, all of which are "range named") in my workbook (containing about 7 worksheets) that I need to apply this procedure to?
Can anyone help me finish my task?
Thank you very much in advance,
Dan
|