Copy & paste in blank cells only takes forever

Joined
May 10, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I am trying to have a macro in my workbook copy data from a column AA and paste it in a different column AB skipping non-blanks, so that if say AB100 is not empty my code won't do anything but move on to copying AA101 into AB101 and so on.
Thing is, depending on the parameters the workbook can load up to 4000 rows and this check is making the execution extremely slow, peaking at 20 mins.
Bypassing the check makes the copy and paste a matter of seconds, but it is required that destination cells that are already filled remain unchanged.
What can I do to make my code run faster?
Here's a snippet of the macro:

Excel Formula:
 Dim lastrow As Long
    Dim rowCounter As Integer
    
    lastrow = ActiveWorkbook.ActiveSheet.Range("H16").End(xlDown).Row
    
    If lastrow > 60000 Then lastrow = 16
    
    Application.ScreenUpdating = False
       
    For rowCounter = 0 To lastrow
     If Range("AB" & 16 + rowCounter).Value2 = "" Then
        Range("AB" & 16 + rowCounter).Value2 = Range("AA" & 16 + rowCounter).Value2
     End If
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi & welcome to MrExcel.
How about
VBA Code:
Sub VerifiedBleachers()
   With Range("AB16:AB" & Range("H" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("if(@<>"""",@," & .Offset(, -1).Address & ")", "@", .Address))
   End With
End Sub
 
Upvote 1
Solution
Hi & welcome to MrExcel.
How about
VBA Code:
Sub VerifiedBleachers()
   With Range("AB16:AB" & Range("H" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("if(@<>"""",@," & .Offset(, -1).Address & ")", "@", .Address))
   End With
End Sub
Used this in my sub and it works just fine while drastically reducing execution time. Thanks a lot Fluff!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,216,156
Messages
6,129,188
Members
449,492
Latest member
steveg127

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
Back
Top