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]

You could try this which calls the function per post#2

Sub SetScrollArea()
    ActiveSheet.ScrollArea = TrueUsedRange.Address
End Sub

 

[Yongle]

If you are only concerned about vertical scroll

Sub ResetScroll()
    Dim r As Long

    r = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ActiveSheet
        .ScrollArea = Range(.ScrollArea).Resize(r).Address
    End With

End Sub

 

[Yongle]

or use last value in column A

Sub ResetScroll()

    With ActiveSheet
        .ScrollArea = Range(.ScrollArea).Resize(Cells(Rows.Count, "A").End(xlUp).Row).Address
    End With
    
End Sub

 

[MagiCat]

Exactly, thanks! Worked perfectly, had always reset usedrange to make it work (which even works on my version of 2016 at home. I wonder if at work it's an early version of 2016 and they hadn't fixed it yet.

You could try this which calls the function per post#2

Sub SetScrollArea()
    ActiveSheet.ScrollArea = TrueUsedRange.Address
End Sub

 

[MagiCat]

Actually, looked at it closer and it doesn't work the way needed. What it does is stops you from editing any cells outside of the range. I need the spreadsheet to function normally afterwards.

 

[Yongle]

Let's approach from a different direction - the user's issue.

UsedRange value is causing the scroll area to be incorrect.
What is the resultant issue for the user? What is user prevented from doing? What does the user want to do that is being made more difficult?

 

[MagiCat]

Generally when selecting an area to copy and paste, I scroll to the top, select the top left cell I want, then use the scrollbar to scroll to the bottom to get the bottom data. But since much of the data has been deleted, I have to go hunting for the data. In addition, when I scroll horizontally looking for the right column, it makes it more difficult to find the right column. It's more annoyance than anything but I know that many people were happy when I gave them the initial macro and have had people come to me because it's not working anymore.

It's not a huge issue, just would like to make it work.

Let's approach from a different direction - the user's issue.

UsedRange value is causing the scroll area to be incorrect.
What is the resultant issue for the user? What is user prevented from doing? What does the user want to do that is being made more difficult?

 

[Yongle]

I am not sure which cell you are starting from but if it is not the first cell in the used range you could try....

Select any starting cell and this selects all cells from that cell to the last cell of real UsedRange

Sub SelectFromActiveCell()
     
    Dim LRow As Long, LCol As Long
    On Error Resume Next
     
    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
    
    Range(ActiveCell, Cells(LRow, LCol)).Select
     
    On Error GoTo 0
     
End Sub

I do not think you want this, but I'm posting it regardless
(Previous function transformed into a sub)
Select the true used range with ...

Sub SelectUsedRange()
     
    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
    
    Range(Cells(FRow, FCol), Cells(LRow, LCol)).Select
     
    On Error GoTo 0
     
End Sub

 

[MagiCat]

Thanks for all of the help. Even if it didn't fix the problem, I got some useful functions out of it for other things.

It does seem that saving the file fixes the problem completely, just that I don't want to just randomly save a user's file. I'm just wondering what part of the save process actually fixes the problem, thinking I could create a solution it I could get it to do all parts of the saving process except for actually saving the file.