How to reset Excel Vertical Scroll Bar length (wit

Question

Part of my program pastes several columns of data in a worksheet, then removes duplicate values in each column (with the variable range to search for duplicate values called rngDelete), then counts the number of unique values. After getting the count values, I delete all the rows in rngDelete.

But I notice that after deleting the rows, my vertical scroll bar length still remains the same, i.e. the same length as when I had data in rngDelete rows. It is true that re-opening the worksheet will reset the scroll bar length, but I was wondering if there is a way to force this to happen upon program runtime completion?

Answer 1

Excuting the following resets the lastcell and does what you want.

ActiveSheet.UsedRange.SpecialCells (xlCellTypeLastCell)