Move merged cell using VBA

Question

I'm very new at VBA, so I find myself in a bit of a hazzle.

I'm trying to move a merged cell up dependent on a specific value in another cell.

Cell D4 contains a value between 1 and 4, and it is dependent on a formula.

When this value is equal to 1 I'd like for the merged cell BQ52:BX64 to move up to row 40, and not replace the cells, but shift them downwards.

When the value is between 2 and 4 I'd like for the cells to shift back to their original location.

I've tried to record macros of me inserting copied cells, but I'm unsure as to how to code this in VBA and how to avoid a loop, since I'm deleting the cells in the recording.

The name of the sheet is "Print Layout"

Any help is much appreciated!

Answer 1

Sub random()

    If Range("D4").Value = 1 Then

    Range("BQ52:BX64").Cut
    Range("BQ40").Select
    Selection.Insert Shift:=xlDown

    End If
     End Sub

This will put the merged cell at row 40 if d4 = 1 otherwise the merged cell will remain there.

Answer 2

If you can name your sheet in VBA something like Print_Layout in the properties window this may help avoid issues in the future. You could then use code such as:

Sub MoveMergedCells()

  Print_Layout.Select
      If Range("D4") = "1" Then
          Range("BQ52:BX64").Cut
          Range("BQ40").Insert Shift:=xlDown
      End If

End Sub

You could also add an If/Then function for values 2-4. Hope this helps :)

Answer 3

As per the answer given by @L Johnstone, complete code can be given as:

Sub MoveMergedCells()

 Print_Layout.Select
      If Range("D4") = "1" Then
          Range("BQ52:BX64").Cut
          Range("BQ40").Insert Shift:=xlDown
      Else If Range("D4") = "2" Then
          Range("BQ40:BX52").Cut
          Range("BQ52").Insert Shift:-xlDown
      Else If Range("D4") = "3" Then
          Range("BQ40:BX52").Cut
          Range("BQ52").Insert Shift:-xlDown
      Else If Range("D4") = "4" Then
          Range("BQ40:BX52").Cut
          Range("BQ52").Insert Shift:-xlDown
      End If
End Sub

I have tried my best to answer this, apologies if anything misses. Thanks!!!