macro help

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
499
Office Version
  1. 2019
Platform
  1. Windows
Greetings - I am using the following code in a workbook to quickly autofill the data on the last row down to the next row....

VBA Code:
Dim LR As Long

    Sheets("Fume Scrubbers").Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LR).Resize(, 21).AutoFill Destination:=Range("A" & LR).Resize(2, 21)

The workbook has 67 worksheets in it (I use the above snippet for each sheet) and the macro runs perfectly on every sheet - except for 1 sheet! I have looked at this sheet intensively for anything that might be different from the others and I can find nothing that is different. There is not any coding on the worksheet itself, there are nothing in one of the cells somewhere else on the sheet, I do not get any error messages, the debugger cycles through fine - just nothing happens when the macro runs - I am stumped. Does anyone have an idea of what I should check for?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows
If you step through the code with F8 when you get to the autofill line what is the value of LR?
 

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
499
Office Version
  1. 2019
Platform
  1. Windows
532, not sure what that means though - 532 values?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows
It means row 532, is that the last row of your data?
 
Solution

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
499
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

oh - not even close, it should be 1495, so apparently I have a blank row up there somewhere and I was looking in the wrong direction. Thank you for the new knowledge and the help Fluff (for like the hundredth time lol)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,197
Office Version
  1. 365
Platform
  1. Windows
It means that you have nothing in col A below A532.
Should A always have a value in every cell? If not is there another column that will?
 

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
499
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff, thanks for replying again - I was just wondering about updating... ;) Scratching my head again because there are no gaps in this column, there is a value in every cell... cycling the code keeps sending it down to the next line 533, 534, 535 - overwriting what is already there.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,374
Office Version
  1. 365
Platform
  1. Windows
Just to confirm, you are looking at column A on your "Fume Scrubbers" sheet, right?
You are saying that there are the last cell with data in column A on that sheet is 1495?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,374
Office Version
  1. 365
Platform
  1. Windows
That just doesn't seem possible. Something does not seem correct here.
Would you be able to upload a copy of your file to a file sharing site (and provide a link here) for us to download and take a look at?
 

Forum statistics

Threads
1,136,909
Messages
5,678,516
Members
419,768
Latest member
eguechi09x

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