Retaining a range definition

[PinkeyNBrain]

Problem: I want to move a range of data but using code similar to

src_range.Cut Destionation:=des_range

I find src_range.Address is changed to match des_range.Address. In some cases my value for des_range is compromized. I want to retain the original definition of src_range.Address. I tried

set org_src_range = src_range
src_range.Cut Destionation:=des_range

but found that my address for org_src_range gets changed as well. I'm not a VBA wiz by any means but did come up with this that is working although clunkey. Looking to see if anyone has an opinion on improving it. I know what I need to add should it be called with negative row/col shift vals. But for what there is now, what would work better? Thanks in advance.

Function shift_data_block(cell_ref As Object, _
                          Optional row_shift As Integer = 0, _
                          Optional col_shift As Integer = 0) As Boolean
   Dim top_left_cell As Object
   Dim src_range As Object
   Dim des_range As Object
   Dim src_rows As Integer, src_cols As Integer

   shift_data_block = False
   On Error GoTo err_exit
   
   Set src_range = cell_ref
   Set des_range = src_range.Offset(row_shift, col_shift)
   src_range.Copy Destination:=des_range


   ' Clear out horizontal component if any
   If row_shift > 0 Then
      Set top_left_cell = src_range.Range("A1")
      src_cols = src_range.Columns.Count
      Set des_range = Range(top_left_cell, _
                            top_left_cell.Offset(row_shift - 1, src_cols - 1))
      des_range.Select
      des_range.Clear
   End If
   If col_shift > 0 Then
      Set top_left_cell = src_range.Range("A1")
      src_rows = src_range.Rows.Count
      Set des_range = Range(top_left_cell, _
                            top_left_cell.Offset(src_rows - 1, col_shift - 1))
      des_range.Select
      des_range.Clear
   End If
   
   shift_data_block = True
   
   Exit Function
   
err_exit:
   Err.Raise Err.Number, Err.Source, Err.Description

End Function

 

[PinkeyNBrain]

Please substitute Destination for Destionation above

 

[Andrzejek]

You may have a better luck to get your answer here: forum707

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PinkeyNBrain]

Sure - thx