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

 

[mumps]

I need to be able to change the sheet name and the cells reference B4:D7 in the VBA.

Can you please explain what you mean by this?

 

[Bob L]

Can you please explain what you mean by this?

Of course. What I mean is the solution you will find will be based on my example, so Sheet1 (name of the tab) and range B4:D7, but the file where I'll use your code, the tab won't be name Sheet1 and the range won't be B4:D7, it will be much bigger. I thought it would be easier to show a little range in the example with XL2BB. I hope my explanation is clear.

 

[mumps]

What may work with sample data may not work on your actual file. It would be better to post a screenshot of a larger sample of data that is more representative of your actual workbook. Better still, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Fluff]

How about

Sub BobL()
   Dim StartRw As Long
  
   StartRw = 8
   With ActiveSheet
      .Range("B" & StartRw & ":D" & Rows.Count).Clear
      .Range("B4:D7").Copy
      .Range("A" & StartRw & ":A" & Rows.Count).SpecialCells(xlConstants).Offset(, 1).PasteSpecial xlValues
   End With
   Application.CutCopyMode = False
End Sub

 

[Bob L]

How about

Sub BobL()
   Dim StartRw As Long
 
   StartRw = 8
   With ActiveSheet
      .Range("B" & StartRw & ":D" & Rows.Count).Clear
      .Range("B4:D7").Copy
      .Range("A" & StartRw & ":A" & Rows.Count).SpecialCells(xlConstants).Offset(, 1).PasteSpecial xlValues
   End With
   Application.CutCopyMode = False
End Sub

It works, but it only copy/paste it values.

Instead it should:
1) Copy/paste (normal, so that the functions recalculate based on cell A)
2) Copy/paste values (there's so many block of cells to recalculate that the file becomes slow, so turning the functions into values help with that)

I can upload the file in question as mumps asked if it would be easier.

 

[Fluff]

How about

Sub BobL()
   Dim StartRw As Long
   
   StartRw = 8
   With ActiveSheet
      .Range("B" & StartRw & ":D" & Rows.Count).Clear
      .Range("B4:D7").Copy .Range("A" & StartRw & ":A" & Rows.Count).SpecialCells(xlConstants).Offset(, 1)
      With .Range("B" & StartRw, .Range("D" & Rows.Count).End(xlUp))
         .Value = .Value
      End With
   End With
End Sub

 

[Bob L]

How about

Sub BobL()
   Dim StartRw As Long
  
   StartRw = 8
   With ActiveSheet
      .Range("B" & StartRw & ":D" & Rows.Count).Clear
      .Range("B4:D7").Copy .Range("A" & StartRw & ":A" & Rows.Count).SpecialCells(xlConstants).Offset(, 1)
      With .Range("B" & StartRw, .Range("D" & Rows.Count).End(xlUp))
         .Value = .Value
      End With
   End With
End Sub

It works as intended in the example in the OP, but when I try in my file, it doesn't copy/paste values the correct one (maybe I modified your code incorrectly).

Here's the file: https://easyupload.io/w5alib

If you look at cells E13 and E133, you'll see the simple concatenate. When I run your code (which is already in the file), E13 and E133 should still be functions and E282 and E431 should be the concatenate values, but it's the opposite, the first 2 are values and every ranges that's been copied isn't pasted values, they're still functions.

Obviously, I only kept one function per range, but it holds true for everything else in the range.

Thanks!

 

[Fluff]

Is there any column that will always have a value, or formula on the last used row?

 

[Bob L]

Is there any column that will always have a value, or formula on the last used row?

As you can see in your code, I copy the range E133:AX278 and the last row (278) will always have a value in the column E:J and formulas in K:AX. The last column (AX) will always have a value for the header (AX133) and formulas in the remaining cells of the range.

Hoping I understood your question correctly.