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

Bob L

New Member
Joined
May 10, 2020
Messages
36
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCD
1
2
3
4A123
5789
67877
7455
8
9B
10
11
12
13
14
15
16C
Sheet1
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Bob L

New Member
Joined
May 10, 2020
Messages
36
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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

New Member
Joined
May 10, 2020
Messages
36
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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

New Member
Joined
May 10, 2020
Messages
36
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
Is there any column that will always have a value, or formula on the last used row?
 

Bob L

New Member
Joined
May 10, 2020
Messages
36
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,439
Messages
5,642,121
Members
417,257
Latest member
Sarahbw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top