usedrange no longer working

[MagiCat]

I have some simple code that has worked for many years to fix the used range within a sheet after deleting a bunch of rows. Now it doesn't seem to work anymore. It was working through Excel 2013 but now that I'm using Excel 365, it has stopped working.

Sub FixUsedRange()
    
    If ActiveSheet Is Nothing Then
        MsgBox "Can't run without an active sheet."
    Else
        With ActiveSheet
            .UsedRange
        End With
    End If
    
End Sub

 

[Yongle]

I prefer not to rely on UsedRange and use a function (credit Jacob Hilderbrand for this method) to get the true used range

Sub GetUsedRange()
     
    Dim TrueRng As Range
    Set TrueRng = TrueUsedRange
    If Not TrueRng Is Nothing Then MsgBox "True used range: " & TrueRng.Address
     
End Sub

Public Function TrueUsedRange() As Range
     
    Dim FRow As Long, LRow As Long, FCol As Long, LCol As Long
     
    On Error Resume Next
     
    FRow = Cells.Find(What:="*", After:=Range("XFD1048576"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
     
    FCol = Cells.Find(What:="*", After:=Range("XFD1048576"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
     
    LRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     
    LCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Set TrueUsedRange = Range(Cells(FRow, FCol), Cells(LRow, LCol))
     
    On Error GoTo 0
     
End Function

Here is a barely modified vesrion of your simple code that you could try

Sub FixUsedRange()
    
    If ActiveSheet Is Nothing Then
        MsgBox "Can't run without an active sheet."
    Else
        Application.ActiveSheet.UsedRange
    End If
    
End Sub

This is more likely to work

Sub FixUsedRange()
    
    Application.Calculate
    ActiveWorkbook.Save
    
End Sub

 

[MagiCat]

Thanks Yongle,

Saving the workbook works great except that in code there are a couple of instances that I'm concerned about. One is that the user might not want the file saved. The other is that I use it in many cases where it's a new workbook that was used to import data and change it around.

The TrueUsedRange function is awesome! Is there any way to get the TrueUsedRange to set the used range to eliminate the scrolling problem?

 

[MARK858]

@MagiCat, just out of interest what results do you get if you run the code below?

Sub resetit()
Dim xx As Long
MsgBox ActiveSheet.UsedRange.Address
xx = Application.ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Address
End Sub

 

[MagiCat]

MARK858, I started with a sheet with 10987 rows and ran it and it correctly showed me the range. I then deleted rows and it adjusted and both messageboxes showed the new range.

I may need to start over with my problem though, it's not with what the UsedRange is reporting, it's the fact that on 2010 and 2013, after running my routine at the top it would make it so that scrolling didn't overrun the data but in 2016 it overruns the data. As of right now, in 2016 the only thing that fixes the scrollbar is actually saving the file.

How can I make it to where after deleting data, the scrollbars work correctly when I drag the slider up and down?

 

[MARK858]

Afraid I haven't got 2016 in front of me until I go back to work on Monday but I can't remember seeing it where the scrollbar goes further than the last row in the usedrange and in the code I posted obviously the usedrange did get reset or else you wouldn't get

I then deleted rows and it adjusted and both messageboxes showed the new range.

.
Although as a further test can you run the you code below before you you delete the rows and then run the code in my previous post after the delete and post the results as I would like to see the difference in the results in the the first code I posted as a double check.

Sub resetx()
MsgBox ActiveSheet.UsedRange.Address
End Sub

I will have a play with it on Monday and see if I can recreate your scenario.

 

[MagiCat]

Sure, the version of Excel that's being ran is actually Microsoft Excel 2016 MSO (16.0.9126.2282) 32-bit (just to make sure we're on the same page.)

I've created a table with 200 rows, A1 has the number 1, A200 has the number 200. When I run resetx() I get $A$1:$A$200. The scrollbar is fine at this point.

Next I delete rows 101 to 200. At this point the scrollbar is acting like there are 200 rows.

Next I run resetit(), the first message box says $A$1:$A$100 and the second message box says $A$1:$A$100. The scrollbar is still acting like there are 200 rows after this exits.

The only way the scrollbar fixes is if I save the file.

 

[MARK858]

I am not expecting this to work but what happens if you you do the same test replace resetit() with the code below?

Sub LoseThatWeightx()

    Dim xx As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    With ActiveSheet
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
        .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
    End With
    
    xx = Application.ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = True
End Sub

 

[MagiCat]

Worked on my home computer with Excel 2016 but a different build (this one is Microsoft Excel 2016 MSO (16.0.10827.20138) 64 bit).

I'll check it out on my work computer by Monday.

Thanks for all of your help!

 

[MagiCat]

Just tried it on my work computer (had forgotten I threw it in my car tonight). Turns out that it doesn't work on that. I'm starting to wonder if it's just a bug in the build of Excel that I'm using at work (just by the build number, it looks pretty old. If you have any further ideas, let me know.