Excel usedrange not updating
This tip (11526) applies to Microsoft Excel 2007, 2010, 2013, and 2016. Row This works with columns containing values; from memory, there may be complications in a column containing formulas which can return blank.You can find a version of this tip for the older menu interface of Excel here: Finding the Last-Used Cell in a Macro. @Andrew, There's several ways to do this dpending a little on how you want to specify the column and what the result is.If you are working in a worksheet, you know that you can press Ctrl End to jump to the last-cell in the worksheet.The shortcut chooses the cell that represents the intersection of the last column containing data and the last row containing data.Being able to use Auto Hotkey to automate Excel via COM is awesome!In this video we walk through using these functions to obtain First row, Last row and # of used rows from Excel via Auto Hotkey.The following Macro code will delete all the rows below and the columns to the right of the Active Cell, then reset the scroll bars.Enter the code into a Module of the Visual Basic Editor.
Thanks Andrew I think you will find that the statement Active Sheet. You can try it by deleting a few rows and testing Ctrl End then running the command in the immediate window and then try Ctrl End again.
It works because apparently Excel, when it calculates the Count property for the number of rows in the worksheet, always resets the "last cell" indicator.
is your source for cost-effective Microsoft Excel training.
I dig this approach and I'm going to play with it for dynamic reporting workbooks.
Right now I'm creating individual sheets and storing counts in variables so that I can produce an "overview" sheet at the end _and_ show where I got the numbers (individual sheets).