Excel usedrange not updating

When you selected a Pivot Table, nothing could be done until all the OFFSET functions updated.

When a spreadsheet tab was selected, nothing could be done until the all the OFFSET functions updated.

I was trying to reset the used range in a spreadsheet template I inherited the other day, that had a much bigger used range than it should have had. Tried deleting rows and columns below and to the right of the Then and only then could I get the used range to go back to it’s cage. But might explain why you’ve had used ranges in the past that you simply could not get to behave.

We’d like to give it custom formatting, so it looks more like a time from a stopwatch.

Hi, I have some code that is supposed to find the last row and column with data and clear the contents. Find("*", searchorder:=xl By Columns, Search Direction:=xl Next). I used your lines in step 2 and it did not find the last column and last row. Some things reset this memory and some things do not! As a workaround, I calculate the range and set the value to double quote "". ) you will see what I mean and maybe from my conclusions get some idea. Specialcells which appeared mostly at its upper limit to be bound by the Last Used Range upper limit. If I get any further I will post here further here ...

Normally, this works, but there are circumstances when this code (see step 3) does not clear the contents. Clear End If End Sub Last Row With Data = Target Worksheet. Find("*", searchorder:=xl By Rows, Search Direction:=xl Next). Instead it read the last row as 1 and the last column as 1. Similarly some things work using the last Used Range and some things automatically update or refresh this memory so that they or following commands looking to find last rows or columns, etc. It's seems sloppy to code this way but for now it works. But you will also see that I did not really get to fully understand it. If I manually removed all entries I then had no Used range. - As you will see from my ramblings at that Thread, there were exceptions to the rules. Up until now most of my Googling just gave answers like “VBA” behaves badly or does not handle well or consistently it’s memory of The last Used Range.” Not much help to us! Here was one google: Excel Developer Tip: Automatically Resetting the Last Cell Alan. Another last “Wild” thought – If you are looking / playing with “empty cells” you may find that looking for “” would give different results as looking for .

I've tried two methods, one that uses a named range and another that creates a range based on the furthest row,column. I watch the commands being built in the debugger and all the variables are getting set correctly. If I look at the sheet I could clearly see that row should be 214 and the column 20. It's a real mystery why step 3 completely ignores the .clear command. However my , Specail Cells Code still “Worked” remembering the last Used Range.... Special Cells(xl Cell Type Blanks) – But again this is just throwing in another wild direction for You to be thinking about!!

The command is executed without a run time error, but the cells do not clear. Find("*", searchorder:=xl By Rows, Search Direction:=xl Previous). The only way I was able to get around this is to set the entire range to a double quote (""). It's when it tries to clear the sheet the second time this occurs. UNLESS I used certain Properties to obtain the Used Range. This was because a By-product of those Properties was to reset that memory. We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic.


Leave a Reply