Get the Actual Used Range in a Spreadsheet – #Excel #VBA


Struggling To Excel

In one of my posts, I wroteabout the UsedRange property of the Worksheet object. I use it in almost all of my spreadsheet applications. Excel keeps track of the last cell youused during your current session, and uses itto determine the used range of asheet.The last cell is the oneyou get to, when you press Ctrl+End.

You mayget to thelast cell in a sheet using VBAby using the SpecialCells method of the Range object:

There is one tiny problemwith using the UsedRangeproperty. Say you had content in your sheet that you removed, or you randomly did some ad-hoc checks in your spreadsheet that you cleared by pressing the Delete button; the “Last Cell” does not get refreshed until you save the workbook. The only sure way to “update” a sheet’sUsedRange is to select all the rows and columns that contained data at some point during your session…

View original post 228 more words