Hi Dan,

Try this

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


Good Luck,

Wouter HM


Dan wrote in message ...
I have two rows in my "TestRange". Currently rows 8 & 20, but they will change over time.
There are currently 10 columns in "TestRange", which could also change over time.

Each column, which happens to start in Column C at the moment, holds data representing the upcoming days of the month. This first column holds data for today (T), the next column to the right holds data for tomorrow (T+1), etc.

Here's my challenge:

1. How can I shift data residing within both rows of TestRange from a user designated Starting Column to a user designated Ending Column? and

2 How can this procedure be done for at least 5 additional ranges all having different names?

Can anyone help!!!!!

Dan

Dear Wouter HM

This works great.

1. How do I get these same values to shift to the right? An

2. How do I loop through multiple ranges (all of which are "range named"), shifting these values within each range?

Thank you in advance
Da

Dan wrote in message ...
Dear Wouter HM,

This works great.

1. How do I get these same values to shift to the right? And

2. How do I loop through multiple ranges (all of which are "range named"), shifting these values within each range?

Thank you in advance,
Dan

Hi Dan,

This should do the trick:

Sub ShiftRightInAllRanges()
Dim intColLeft As Long
Dim intColRight As Long
Dim celLoop As Range
Dim varOld As Variant
Dim varNew As Variant
Dim strNames As String
Dim intNames As Integer
'

' loop for all named ranges
For intNames = 1 To Application.Names.Count
strNames = Application.Names(intNames)
' Determen left most column for range
intColLeft = Range(strNames).Cells(1, 1).Column
' Determen right most column for range
intColRight = Range(strNames).Columns.Count + Range(strNames).Column - 1

For Each celLoop In Range(strNames).Cells
If celLoop.Column intColLeft Then
' Shift cells witnin range 1 column to the left
varOld = celLoop.Value
celLoop.Value = varNew
varNew = varOld
If celLoop.Column = intColRight Then
celLoop.Offset(0, 1).Value = varNew
End If
Else
' clear cel in last used column
varNew = celLoop.Value
celLoop.ClearContents
End If
Next celLoop
Next intNames
End Sub


I am pleased to help.