Moving Data Within a Range
 

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

Moving Data Within a Range
 

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

Moving Data Within a Range
 

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.