Shift constant values in a range left or right by n columns

[Dirk Goldgar]

Hi, Excel folks -

Given a range containing some cells with constant values and some cells with
formulas, what is the most efficient way to shift only the constant values
left or right by a specified number of columns, leaving formulas and all
formatting in source and target cells unchanged? I'd also want to clear the
values in the cells from which the data has been moved.

What this is all about is a worksheet that contains columns representing
months of data, with the rows representing line items or various subtotals.
I want to advance or retreat the date window by a user-specified number of
months.

I've worked out how to do this by looping through the rows and columns of
the range, but (a) the code is slow, taking about several seconds to
execute, and (b) it seems to me this is not the "Excel" way to do this. I
feel like there ought to be a set of simple range operations to accomplish
it.

Can you advise me, please?

 

[ML0940]

Hi,
For the first part of your question, this may help.
I have a budget spread sheet and this is how I handle it:

I am using a named range, but you can also use a hardcoded range as well;
then I use the offset to copy what I need, then I use paste special
(PasteValues)
If you use PasteValues, then the values will be pasted indentically to the
copied values

'Copy Column C bills
Sheets(1).Activate
Sh1bW.Offset(0, 2).Copy
'Paste bills one column to the right of named range Sh2bw
Sheets(2).Activate
Sh2bw.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

For the clear part, it is your source range .ClearContents

Also, I actually have a macro callled Shift Left, see if this helps you as
well
Between the two, you should be able to piece something together.

If not, I will try to help you more

ML

Sub ShiftLeft()

Application.ScreenUpdating = False

Dim i As Integer

On Error Resume Next
'If the cell in column C - the cell in Column B is <= 0, Column B (cell) is
null
For i = 6 To 106
If (Range("B" & i).Value - Range("C" & i).Value) <= 0 Then
Range("B" & i).Value = ""
Else
'Deduct the amount paid that week (Column C) from The Amt.Due (Column B)
Range("B" & i).Value = Range("B" & i).Value - Range("C" & i).Value
'Change cell format in Column B back to accounting
Range("B" & i).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
End If
Next i

'Shift weeks to the left
Range("C3:C106").Select
Selection.ClearContents

Range("D3106").Select
Selection.Copy
Range("C3").Select
ActiveSheet.Paste

Range("E3:E106").Select
Selection.Copy
Range("D3").Select
ActiveSheet.Paste

Range("F3:F106").Select
Selection.Copy
Range("E3").Select
ActiveSheet.Paste

Range("G3:G106").Select
Selection.Copy
Range("F3").Select
ActiveSheet.Paste

Range("G3:G106").Select
Selection.ClearContents

'Values for column "G3" through "G5"

'Copy The formula in "F5" to "G5"
Range("F5").Select
Selection.Copy
Range("G5").Select
ActiveSheet.Paste

'The net pay in "G4" = The (Bi-Weekly) next pay in E4
Range("G4").Value = Range("E4")

Dim IntervalType As String
Dim Number As Integer
Dim FirstDate As Date

IntervalType = "d" '"d" specifies days as interval.
Number = 7
FirstDate = Range("F3").Value

'The date in "G3" = The date in "F3" + 7 days
Range("G3").Value = DateAdd(IntervalType, Number, FirstDate)

Range("A2").Select
Selection.ClearContents

End Sub

 

[Dirk Goldgar]

Hi,
For the first part of your question, this may help.
I have a budget spread sheet and this is how I handle it:

I am using a named range, but you can also use a hardcoded range as well;
then I use the offset to copy what I need, then I use paste special
(PasteValues)
If you use PasteValues, then the values will be pasted indentically to the
copied values

'Copy Column C bills
Sheets(1).Activate
Sh1bW.Offset(0, 2).Copy
'Paste bills one column to the right of named range Sh2bw
Sheets(2).Activate
Sh2bw.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

For the clear part, it is your source range .ClearContents

Also, I actually have a macro callled Shift Left, see if this helps you as
well
Between the two, you should be able to piece something together.

Thanks, this is the sort of thing I was looking for. Does this work,
especially the Offset, if the range is discontinuous? Given that I want to
ignore rows that contain subtotalling formulas, I thought I might use
SpecialCells to get a range containing only those cells that have constant
values -- but that would be a discontinuous range.

By the way, I noticed this in your ShiftLeft macro code:

Range("B" & i).Value = Range("B" & i).Value - Range("C" & i).Value
'Change cell format in Column B back to accounting
Range("B" & i).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"

In testing my own looping code, I found that setting a cell's Value property
seemed to reset its format, and so I used code to save the format and then
restore the format after setting the value. I wasn't sure that I ought to
have to do that, and thought maybe I was missing something. But your code
suggests that losing the formatting is just something that happens, and you
have to deal with it. Is that right?

Thanks for your assistance,