VBA to delete then copy/paste cells when there's something in column A

[Bob L]

I'm looking for a way to delete everything below row 7 (except column A), then copy/paste B4:D7 each time there's something in column A and then copy/paste it values.

So, in the example below, delete everything below row 7 (except column A), copy/paste B4:D7 to B9 and B16 (because there's something in cells A9 and A16) and then copy/paste value everything below row 7. I need to be able to change the sheet name and the cells reference B4:D7 in the VBA.

Thanks.

Book1
	A	B	C	D
1
2
3
4	A	1	2	3
5		7	8	9
6		7	8	77
7		4	5	5
8
9	B
10
11
12
13
14
15
16	C
Sheet1

 

[Fluff]

Ok, how about

Sub BobL()
   Dim StartRw As Long
   
   StartRw = 279
   With ActiveSheet
      .Range("E" & StartRw & ":AX" & Rows.Count).Clear
      .Range("E133:AX278").Copy .Range("A" & StartRw & ":A" & Rows.Count).SpecialCells(xlConstants).Offset(, 4)
      With .Range("E" & StartRw, .Range("E" & Rows.Count).End(xlUp).Offset(, 45))
         .Value = .Value
      End With
   End With
End Sub

 

[Bob L]

Ok, how about

Sub BobL()
   Dim StartRw As Long
  
   StartRw = 279
   With ActiveSheet
      .Range("E" & StartRw & ":AX" & Rows.Count).Clear
      .Range("E133:AX278").Copy .Range("A" & StartRw & ":A" & Rows.Count).SpecialCells(xlConstants).Offset(, 4)
      With .Range("E" & StartRw, .Range("E" & Rows.Count).End(xlUp).Offset(, 45))
         .Value = .Value
      End With
   End With
End Sub

Yes, it works perfectly!! Thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.