How to get the Last Row in VBA(The Right Way!)

[@Excelmacromastery]

FLASH SALE: 25% off my Effective Excel VBA course before Saturday 14th September: 👉courses.excelmacromastery.com/courses/Effective_Excel_VBA Let me know what you think about the methods in the comments. Make sure to download the source code with examples and the custom functions that I created.

[@mikheilchigogidze4224]

Can you please also make Python channel. Sooner the better

[@khalidalisawi8037]

pleas how I can download the source in this great video.

[@khalidalisawi8037]

I have seen many videos but many codes just in your video especially to find the last cell and to select range with using the last row and last column as a reference to a cell. thank you for your work and how to download the codes and examples

[@thomaswaldmann9264]

Excellent. One small problem - if the sheet is empty it will find nothing and give back a range with nothing -> Err = 91. if range is nothing then Last = 0 else Last = range.row endif

[@fullpowerrolf]

Thanks a lot for the video. I tried it out, and I think it does not count merged cells. So if the last unmerged cell is in C3 and there is a merged cell from D4 to E4 your last cell will be C3. Find function does not properly work with merged cells I think.

[@Metalocif]

Didn't know you had a KZbin channel. I teach VBA, and I always point my students to your website as the best resource there is.

[@Excelmacromastery]

Thanks Alexandre.

[@tha2irtalib343]

Thanks Paul for sharing such a great job , no one like you .. no one ever .

[@rogerirvine4420]

In the final example, I love the fact that you can (if required) insert some starting point coordinates. Excellent. Seeing your patient build-up was worth the wait. Thanks Paul. I've learnt a load of real, 'outside the box' approaches to many things that are useable and adaptable in 'real world' settings since I signed up for your course. (And I've promised myself that I WILL get to finish it!)

[@Excelmacromastery]

Glad you like it Roger.

[@rogerh2694]

Irvine, CA?? Also in OC!

[@LuizFeijotheGuitar]

@@Excelmacromastery hi sir Paul, i love Excel, and i starting to learn about VBA, from Where i Begin?

[@free3690]

Such an elegant solution, thank you for sharing your wealth of knowledge!

[@KurtSchwind]

I've probably solved the 'last Row' or 'last Column' a dozen ways and none of them are as clean and consistent as that 'Find' method. Thanks.

[@cpro671]

The find method is an interesting way to approach this problem and it works well in most circumstances. But in testing I did find that if you' are working with a protected sheet that also has the Hidden checkbox selected, the functions will generate an error and return Cells (1,1) from the error handling code. If you manually hide rows or columns the functions work correctly. Also, if filters are turned on you will get the last filtered row, not the last of all rows. Returning the last filtered row could potentially be useful as long as you are aware and not looking for the last actual row of data.

[@АлексейСоков-ь8и]

Thanks for your note!

[@YDysgwrAraf]

Working with and navigating our way around unstructured data is an absolutely key skill for spreadsheet developers, and all of these techniques have a place in out toolbox. Knowing the appropriate tool for the job in hand is the crucial thing to learn. I recently worked on a Java project where we were reading spreadsheets into Java library objects, and my fellow developers were astonished by the ease with which I could, using knowledge from years of work with Excel, find my way around the jagged and unpredictable data.

[@PraiseTheLord1611]

How dare you defeat all our "tried and true" methods! ;) Thanks for the video

[@TheGallantTitan]

I struggled with the issue of not properly selecting an area of Jagged data until I watched this video. Thanks for helping me out.

[@Monochromatic_Spider]

Missed a big one with .end(xlUp). It works on VISIBLE rows. So if you have filter mode on and some content rows are filtered out then you're not actually finding the last content row, just merely the last visible content row. Certainly something to keep in mind. Still, this is my general goto because of how quick and simple it is. As for Find, I suspect (without having checked) that it has some performance overhead. It uses the regular workbook "Find and Replace" function to look through content but of course stopping after the first result of literally anything should help. Do keep in mind that the search term shows up if users manually open the Find and Replace function.

[@SolidSnake59]

Well I thought about performance as well, but after all this are only 2 searches, so it shouldn't be so bad.

[@joukenienhuis6888]

Thank you for this very helpful video. I think the lastRow function is especially handy when you want to enter new data. But then again if you work with named Data Ranges in Excel, it can automatically add the last entered data if you are using a form

[@rjaquaponics9266]

This video was incredible. It's like juggling, while smoking a cigar, while riding a bike, while skiiing down fresh powder all at the same time! Bravo!

[@Excelmacromastery]

Thanks

[@mariaalcala5159]

Great method to find the last row! Thanks for sharing your knowledge!!!

[@eduarchavarria]

Paul, great as always, thanks. Searching for the last row of data is one task that I perform very often.

[@hotflashfoto]

Most of the macros I manage were inherited, and thankfully they have column A fully populated with no spaces. However, they were written in the days when there were only 65,536 rows. Recently, there was data that went past 70,ooo rows and it decided that the last row was row #1 until I updated it. At first, I was skeptical of a better way, but using Find looks like a good way of doing it regardless of sheet length. Thanks for sharing!

[@itsdaj]

Thanks for the great content. Where was this video 15 years ago?

[@johnwayne8059]

That's simply great! Well done Paul!👍👍👍

[@Excelmacromastery]

Thanks John

[@aldofernandez2013]

Thank you so much! you dont have idea on how much this will help me

[@jimfitch]

Really looking forward to this, Paul!

[@Excelmacromastery]

I hope you'll like it Jim.

[@jimfitch]

@@Excelmacromastery Like it? I love it, Paul! I interrupted workday to watch this instead of viewing in evening/weekend (usual practice), took detailed notes while watching, & already downloaded source code. I plan to add your code (modified to my naming & commenting practices) to a class module that I created for often-used subs/functions; it works like Access's DoCmd. Thank you for walking thru the first 4 methods before teaching Range.Find. I've used different approaches in the past, but now will standardize on your code. Thank you for sharing this with us!

[@sasavienne]

Thank you so much for sharing this great video. The knowledge you share is priceless.

[@woodpigeon01]

Good one Paul. It’s one of the first question you ask yourself when you are writing Excel code.

[@julianevans2256]

Excellent presentation Paul !

[@zaksept]

Perfect timing, I was just doing a job today and I needed to copy 30 sheets into one. They all had different amount of rows ranging from 20 - 300. However, xlDown woudn't work because the last row had data in random columns, not always col A. What I ended up doing was a for loop where I pulled 300 rows from each sheet, and paste them into the summary sheet. Afterwards I ran a separate macro to delete the blank lines with nothing on them. This would have saved me a couple steps.

[@Excelmacromastery]

Glad it was helpful Zach.

[@amennkonou7161]

I have been waiting for this longtime.Thank you Mr Paul KELLY

[@Excelmacromastery]

You're welcome

[@Info-God]

Great and useful help for those who do heavy programming in VBA-Excel. Thanks Paul.

[@Excelmacromastery]

You're welcome

[@renemarot544]

Thanks. Very didactic. Nerver thinked about using find this way. One cave at with find is hidden rows and hidden columns. If you have some of them find just seems to ignore them.

[@edrobinson8248]

absolutely brilliant. note that we can use Clear All to remove formating rather than having to close the whole WorkBook? :-)

[@officetricks6303]

Got the most wanted video at the right time.

[@pbs36]

As usual, great choice of topic for a video and good solution for the problem..

[@khalidalisawi8037]

really it was a great video that I saw. I saw many videos before but it was the best.

[@rogerio067072]

🤯🤯🤯🤯 wow you never disappoint me in always bringing new knowledge to what i have been doing. Outstanding!!! Will try this method with my data. I always need to check the amount of data i have to deal with and this will help me a lot

[@Excelmacromastery]

Thank you!

[@scottgaines2677]

Another great video Paul!! Very informative! Thanks so much 👍🏻😃

[@Excelmacromastery]

Thanks Scott.

[@ScholarGypsyOx]

Interesting as always, but I do wonder if this is a bit of a sledgehammer to crack a nut. When reading in data that may have lots of blank cells, I design the sheet so that it has a row of headers at the top, with no gaps, and then a "ref" or similar field in the first column which is non-empty for every valid record, and a blank cell denotes the end of the data. (one could automate this of course, using code on the sheet to detect when changes are made and check column 1 is non empty, unless the cell below contains "ZZZ"). Then I use something like set rg = range(sh1.range("top_left").end(xltoright), sh1.range("top_left").end(xldown)), although I think current region would work fine and normally give the same results (except where one has a row with a blank in column 1...).

[@canews8971]

Wow! excellent video. Instant sub. Thanks!

[@itlife4555]

could you please tell what's the screen recording tool you used? it seems perfect.

[@patricknicholson5556]

Excellent compare and contrast. Do you have advice on how to handle/trap those cases where the range you're testing might be empty?

[@patricknicholson5556]

NM. Your eh: captures the situation perfectly.

[@Scrags-Gudale]

Excellent video. A nice addition would have been showing people how to turn the returned column number into a letter.

[@troysiemers]

Actually, I have some sheets where I want to count cells with no data but only color format so the UsedRange "bug" is actually a feature. Very nice!

[@Excelmacromastery]

It does work perfect for that:)

[@gianlucacolangelo8204]

this is absolute awesome!! thank you very much!!!!

[@edrobinson8248]

the problem with USedRAnge is not so much the formated cell but when you clear (of formats as well as contents) the formatted cell. It still remains in the USedRange unless you properly delete it ;-) BTW: Excellent video as usual.

[@Excelmacromastery]

Glad you like it Ed

[@NoName-Since-2022]

Paul, please explain also how to get FIRST column, FIRTS row in range. Thanks in advance!

[@ahmed007Jaber]

Amazing must watch it again. Are you on linkedin?

[@davidunger7425]

Great video Paul, thanks.

[@josejn2007]

Thanks for sharing.

[@josebejaranop4005]

I long to see this premiere Greetings from Santa Cruz Bolivi🇧🇴

[@Excelmacromastery]

Thanks Jose

[@karnabudhathoki5311]

@ Excel Macro Mastery...I have seen your videos on class modules but I would love more.......... not specific but about anything But organising application as a whole because I have faced problems while altering or adding features to already functioning applications..

[@Excelmacromastery]

Class Modules is a big topic. I may do more in the future on some of the concepts involved in designing classes.

[@stephencorbeil9538]

Good job Paul! Seriously going to be helpful with a future project.

[@jhgpsimons]

Never thought of it using find for that, thanks

[@Excelmacromastery]

You're welcome.

[@robbaron1206]

Find is a no-go for me because it changes the settings that users use on the front end. When I hit Ctrl f or Ctrl h I expect that the settings from my last search haven't changed. I had a coworker use a vba find with a whole workbook setting. Then I would try to replace on a sheet and it would replace in the workbook. Very frustrating.

[@davescott7680]

Just use Public Function ResetFind() Dim r As Range Set r = ThisWorkbook.Sheets(1).Cells(1, 1) r.Find What:="", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False r.Replace What:="", Replacement:="", ReplaceFormat:=False End Function

[@shakhobiddinnakiev6767]

does it work in listbject ?

[@davidunger7425]

Paul, this was an excellent video, thanks very much. However, the download link doesn't seem to be working, tried it for the last 3 days, just "spins".

[@AL-tl7ov]

how to do ctrl+arrow down as loop until last cell found in vba? that would be appreciated

[@soumyodipkanrar3032]

Thanks! Very useful video.

[@geertvancompernolle2586]

Download link in the e-mail received doesn't seem to work (anymore?).

[@mihalydozsa2254]

How can I find the last row if there is an expanded array formula in the longest column?

[@Rice0987]

Nice work! But how effectively to use Find method in code?

[@szidzse]

For each cell in range? Could be 1 row, or column. Im using that since i've met with an anomaly (same method /end(xlup).row /different row values in col a or col b, last row were the same)

[@itsdaj]

The link to the code is not loading. Any chance it will get fixed?

[@xaviereng5754]

hi thank u for the lesson. idk how to code for my project and need help. 2 given arrays are list of discounts for office supplies and furnitures Using macro, we needa extract the correct discount from the 2 arrays and match it with the correct product in excel sheet. to be coded into a blank row of discount for the lists of products in excel sheet.

[@elecirlisboa9155]

Excelente aula, parabéns!

[@Josxou]

Do your methods for finding the last row and last column work with merged cells or hidden rows/columns?

[@yeahnick4260]

I would like to know why you are using LookIn:=xlFormulas and not LookIn:=xlValues ​​because xlFormulas returns cells containing functions?

[@UzmanExcel]

Last method has a slight disadvantage. It gives an error while there is no data at sheet instead of one. So, you can wrap up your function with an if statement wheter sheet contains data or not by using native counta function

[@geoffstrickler]

Conclusion, there is no simple way to determine the last row or column containing data in Excel or VBA. There are ways, just no simple, reliable function unless you write one, or include a third party library. Still, his was a useful video for several reasons: 1. It includes the code for such a function, which as you note, needs an exception handler for an empty worksheet. 2. It shows the strengths and weaknesses of the 4 commonly used methods. The fact that two of them don’t work on protected worksheets means I will never use those two methods. Not every worksheet is protected, but I refuse to use methods that will fail when I do have protected worksheets enabled. 3 .end(xlup) and end(xltoleft) look to be the most generally useful, simple functions, as long as you’re aware of their limitations regarding “jagged” data in the final row/column. When you can’t ensure the final row/column will always contain in one specific column/row, then Paul’s functions with your modification is the way to go.

[@Pedritox0953]

Wonderful videos !

[@Excelmacromastery]

Thanks

[@strannostrannovasrr]

Does it also work if the last row(s) with data are filtered or hidden and you stil wants to know the real last row on the sheet?

[@nenavatjyothi3968]

I have one doubt, regarding vba macro not working properly when I run on more than 100 rows. Can You plz help me how to resolve it?

[@deshmukhvv]

i am not able to see code at given link

[@robertowerneck6902]

Great Video! Thanks! 👍

[@kleber_bispo]

hi. the link to source code isn't working.

[@luca1319]

Could you please explain the meaning of "on error goto eh"?

[@TSSC]

The TOC doesn’t mention working with Excel tables (ListObject). Will you cover this as well?

[@Excelmacromastery]

When using a Listobject(Table) you get back a range using DataBodyRange. You can treat it like the range from CurrentRegion and Used Range.

[@TSSC]

One needs to remember that the list may contain no data (only header row) and then referencing the DataBodyRange won’t work.

[@Excelmacromastery]

@@TSSC You can use ListRows to check if the table has no data. If Table.ListRows > 0. I may do a video on Tables in the future. It's worth at least one full video.

[@TSSC]

Exactly. I guess the video would be appreciated. ListObjects are quite handy.

[@mdf9035]

I have multiple sheets in one workbook.I need to combine all sheet as one master sheet and whatever I modify in master sheet it should reflect changes in respective sheet automatically

[@govindkumarsodani3290]

Very useful

[@miless2111sutube]

another excellent video, thank you. One quick question; you were using the shJagged to refer to the sheet "Jagged data" how does this work? I was expecting you to have to type worksheets("jagged data") but ShJagged is much cleaner.

[@coloringontheline]

Option Explicit Private Const cTxt as string = "I found: " Public wb as Workbook Public ws as Worksheet Public sub exmplSub() Dim rng as Range Dim STxt as String Set wb = Thisworkbook Set ws = wb.sheets(1) Set rng = ws.cells.find("*") If Not rng = Nothing Then STxt = rng.value2 Elseif ws.Usedrange.Cells.Count

[@coloringontheline]

Apologies if I'm being annoying but I wanted to give an example of what I meant by explicit declarations and the module and procedural level. Please also forgive me if 2 more things: 1 If I assumed too little of your knowledge; and 2 if that example isn't perfect. Im pretty sure it's right. But I just hand typed it on my phone directly into the comments lol. I hope you or literally anyone reading this finds it helpful even a tiny tiny bit. Im gonna go now 😅

[@miless2111sutube]

@@coloringontheline Thank you Zachary for replying however I am sorry that my skills don't allow me to understand how this response addresses my question about using shJagged to refer to the sheet "Jagged data".

[@coloringontheline]

@@miless2111sutube got it, okay. No worries. My fault. Let me see if I can try again. If this doesn't help maybe I can make a 1-2 min video on it or something. I'm not a KZbinr so it wouldn't be professional. But it might break the communication issue. Here: Windows Hotkeys. (Or use the View menu at the top of the VBA Editor window) Property Explorer = Ctrl+R Property Window = F4 "(Name)" is the first "Property" listed in the "Property Window" when you select "Sheet1" of your VBAProject from the "Project Explorer". "Sheet1" can be found when the section "Microsoft Excel Objects" of your VBAProject is expanded. Which by default, it should be. Press Ctrl+R to select the Project Window. Use your Up and Down Arrow Keys to highlight "Sheet1." Then, Press F4. (not Alt+F4 lol, that closes windows) once you've pressed F4. The property window will either be selected if already open, or it will open if it was closed. The item selected after pressing F4 is the "(Name)" property. You can either click on it, or simply press F4 again. Then, start typing a new name. Whatever you name an objects "(Name)" here is called it's "Codename" and it is what's happening, and how he refers to the sheet as "ShtAnything" So if the first Worksheet in your Excel is called "Catalog List" And you changed the "(Name)" to "CataList" You could use all of the following: Worksheets(1).Range Worksheets("Catalog List").Range Sheets(1).Range Sheets("Catalog List").Range CataList.Range You cannot refer to it as: Thisworkbook.CataList.Range Workbooks("Book1").CataList.Range If you delete the worksheet. The (Name) property doesn't transfer to the next sheet. Meaning. If you use the (Name)/Codename in your code a lot. You'll want to not delete the sheet. If you plan to delete it. Try using the Declarations I talked about earlier. You can only change (Name)/Codename during "Design Time" meaning you can't make a macro change (Name). You have to type it by hand for each object you want to name. It's not a recommended convention for programming for the reasons I mentioned earlier. However, that doesn't mean that you can't or shouldn't do it if it's what's best for you or your projects. Happy to help more if this doesn't work out or if I didn't communicate well again. Links below are to Microsoft Docs Reference Material about this question. Note: this does mean that you can actually just use the default codename from the get-go. Like, Sheet1.Range No change to anything necessary. docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/use-the-properties-window docs.microsoft.com/en-us/office/vba/api/excel.worksheet.codename

[@ryangraham6878]

@@coloringontheline dude. you are the real MVP here. a year later, helping me out. I used "Sheet3" and it worked for me.

[@martinstannard2859]

Great video as always Paul! BTW, just think that method could be slightly improved too…. Firstly, I’d run a “dummy Find” afterwards in the Function, to set the Find arguments back to default (otherwise, next time the User tries to use Find in that session, they will unwittingly use these quirky argument settings of course). Also, the other small downfall I find with this method is that Find will not work on a protected sheet if the cells formulas are set to Hidden (even if the cells have just values, it still won’t work). A way around this is to check using CountA, this would normally be too slow to loop though each row from the last row backwards, but I find that you can get this to run really quickly by using a loop that uses a “shrinking range” as you check.

[@Excelmacromastery]

Thanks Martin. Some good ideas there.

[@martinstannard2859]

@@Excelmacromastery no probs Paul, btw I work in VBA pretty much full time, and your videos have really helped me out along the way. Big thanks:)

[@JelleWie]

Why no speed comparison? That's the biggest thing I care about in such function atm

[@BroersJo]

if filters are turned on you will get the last filtered row, not the last of all rows. Returning the last filtered row could potentially be useful as long as you are aware and not looking for the last actual row of data. Is there a solution for this problem?

[@JeffJefferyUK]

But define "wrong"! If my method (which I'm happy to grade as 'clunky', by the way!) works, then it's not wrong, am I right?

[@Excelmacromastery]

Which method is it? Why not use the best one. It isn't any more effort.

[@JeffJefferyUK]

@@Excelmacromastery I agree. I was mostly teasing. :-) My method is laughable. Essentially it assume/relies on a contiguous range of data, then tests 'Is this cell blank? No? Move down one cell and try again.' until it finds a blank cell. It's risky!

[@TSSC]

=RIGHT() Also, it happens that I add data to a list and need to find the last row. I think I, in most cases, would avoid populating data via code below an existing list. Depending on the situation, I’d either add it in a new set of columns (separated from the existing data, possibly defining a name for the top-left cell to facilitate future referencing) … or simply use a new worksheet. Contiguous data. By the way, pedagogic video (as expected).

[@stephenmackenzie9016]

Would be good to know how autofilter affects any of the methods…

[@mchaudhary4510]

Hi - Find method doesn't work if data on a sheet is filtered, in that case we need to clear the data but since sheet is protected so find will also not work, so we can write another function based on usedrange and counta worksheet function that will work in each situation

[@Pankaj-Verma-]

Thank you.

[@BGHmariam]

I am getting a subscript out of range error with the cells.find method

[@muhittinyetgin97]

I need to use the same program on different computers. It works fine in one but some codes not working in the other.Especially if the codes starting with "application" then its not working.why could it be.

[@tknaved]

Hi, i need a help....how can i make range a varible insted of using Range("A1:D4") ...i want A,1,D,4 all must be daynamic variable kindly support

[@pacman22XD]

What about the speed?

[@mkparker99]

I've been trying to get this to work with a spilled array, but it only goes to the row with the formula and ignores the spilled values. Is there a way to pick these spilled values in the lastrow/lastcolumn/lastcell functions?

[@bxyhxyh]

change xlFormulas to xlValues

[@mkparker99]

@@bxyhxyh Thank you!

[@peterwrth5640]

As always, great tutorial Paul. I got excited at seeing functions which will allow me to protect sheets and return "currentregion" which otherwise fails. But, cannot seem to download the code. Have no ad blockers active. Link sends me to a youtube page with your icon for watching the video. Tried creating the functions myself, but i'm missing something. Any solution would be much appreciated. I really need these functions.

[@Excelmacromastery]

Send me an email and I'll send you the code. Paul ExcelMacroMastery

[@MohAboAbdo]

Thank you so much

[@saviodcunha8741]

Thanks !!

[@Saleh.Wardak]

How to find last row of printing page

[@robertw236]

Great insights into the failings of native methods and very useful method to get around them. Extremely useful. Thanks for sharing your insights with the community.

[@wayneedmondson1065]

Hi Paul. Great tutorial! I tend to use the Range.End method in simple cases when I know the column or row to test and the Range.Find method when needing something more bulletproof. I've seen the Range.Find method written including the argument: After:=Range("A1") to force the search to start from the last row / col of the worksheet, given that the SearchDirection is defined as xlPrevious. If the After:= argument is omitted, is there a default starting point.. maybe from the current location of the pointer? Just curious if it is needed and or makes a difference. Thanks again for all of your great videos. Always learn something fun and new :)) Thumbs up!!

[@Excelmacromastery]

Thanks Wayne. The default starting cell(i.e. After) is the Top Left cell of the range. If you are using the Excel Find Dialog then the default After cell is the active cell. See excelmacromastery.com/excel-vba-find/#Using_After_with_Find

[@wayneedmondson1065]

@@Excelmacromastery Awesome! Thanks Paul :))

[@HonStuartK]

When I tried the "best" (i.e. the "find") solution it didn't work for my last column. My last column was W but it reported U. (V was empty). When I examined it I saw that some of the W cells were merged with X cells. When I un-merged W & X it worked. While I would understand if it had reported W or X, I'm not sure why the find method didn't report either of those columns and stopped at U instead. So this is certainly a drawback to that method.

[@srider33]

To me, the major issue with xl(up/down) method is that it ignores hidden rows. I also use find -- the only draw back is that it messes with my settings the next time, but that's an acceptable penalty compared to all the others.

[@davescott7680]

I just created a function that resets the settings. Problem solved.

[@luapnagle]

wow, I'm 62 and just getting into VBA, not sure I'm getting into and EOL language. I looked at all your options and thought I should share one I use for selecting report print areas Is it simpler than your option ? It will fail with jagged data, correct ? 'Declare variables Dim startcell As Range Dim Lastrow As Long Dim lastcol As Long Dim sh As Worksheet 'Set objects Set startcell = Range("B2") Set WS = Sheets("data") 'Find last row and column with data Lastrow = WS.Cells(WS.Rows.Count, startcell.Column).End(xlUp).Row lastcol = WS.Cells(startcell.Row, WS.Columns.Count).End(xlToLeft).Column 'Select the dynamic range WS.Range(startcell, WS.Cells(Lastrow, lastcol)).Select

[@thenebu]

Great Video ! as always! Just can't download the Code, cause the site isnt loading. Update: it was my adblock. Your site ist now "excepted" :D

[@muhammadhanifsoomro3166]

Sir if data is long or short Lastrow how to automatically select lastrow when generated PDF report ۔۔so how does it's work ۔۔

[@edwar368]

You make a lot of mention of "Protected sheets", assuming you have some control over the actual workbook, you can protect the sheets via VBA and by doing that you have the option of using the UserInterfaceOnly:=True with the Protect method, which allows most VBA activity to run against protected sheets. If I control I workbook, I generally have a "ProtectSheets" macro that runs automatically when the workbook is opened, to apply this setting

[@rrrraaaacccc80]

Great 💯👍