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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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,163
Messages
6,129,223
Members
449,495
Latest member
janzablox

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