Macro runs fully in debug mode, but partially only when played

PraRan

New Member
Joined
Feb 21, 2019
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a simple macro to copy-paste formula over a range of active rows. I've used Dim statement to identify the last active row using worksheet countA function. And then written statements to copy formula from a specific location and paste in a specific cell for the active number of rows.

Dim LastRow as long
LastRow WorksheetFunction.CountA(Sheet1.Range("A:A")) + 2 {I need the +2 as there are blank rows at the top of the report that need to be counted in the total number of active rows}
Sheet1.activate
Range("A2:F2").Select {A2 to F2 have vlookup formula}
Selection.Copy
Range("Q5:AH" & LastRow).PasteSpecial

When I click the button to run the macro, it will run for a partial range. For example if the report is 32,500 rows long, formula will be pasted into 15,000 rows. However when I step in to debug the paste function will be applied to the entire range of 32,500 rows.

What may be going wrong?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
Are you sure there are not more blank cells somewhere down column A?

What happens if you change the calculation of LastRow to this?
VBA Code:
LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
you are missing an "=" sign

VBA Code:
LastRow  = WorksheetFunction.CountA(Sheet1.Range("A:A")) + 2
 

PraRan

New Member
Joined
Feb 21, 2019
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for replying Diddi and Peter_SSs. There's an = sign when defining LastRow.
What's stranger is that the code runs perfectly fine when my coworker runs it on their computer. But not on mine. On mine the code runs only partially. We have the same version of XL. I've tried stepping in and running it through debug to see where it falls apart. But the code runs fine in debug mode. It's just leaving me very baffled. So bizarre.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
this might seem like an odd thought, but have you checked your hard drive. if it is highly fragmented or very full, this task which is quite hdd intensive would run when there is enough time for hdd to keep up, but not at full speed
 

PraRan

New Member
Joined
Feb 21, 2019
Messages
11
Office Version
  1. 365
Platform
  1. Windows
this might seem like an odd thought, but have you checked your hard drive. if it is highly fragmented or very full, this task which is quite hdd intensive would run when there is enough time for hdd to keep up, but not at full speed
Thanks.
Uninstall and reinstall seemed to do the trick for now. But will keep this in mind if I run into similar issue in future.
 

Forum statistics

Threads
1,136,852
Messages
5,678,120
Members
419,746
Latest member
tysonboy82

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