Search code examples
excelvba

Getting the actual usedrange

I have a Excel worksheet that has a button.

When I call the usedRange() function, the range it returns includes the button part.

Is there anyway I can just get actual used range that contains data?

Solution

  • What sort of button, neither a Forms Control nor an ActiveX control should affect the used range.

    It is a known problem that excel does not keep track of the used range very well. Any reference to the used range via VBA will reset the value to the current used range. So try running this sub procedure:

    Sub ResetUsedRng()
    Application.ActiveSheet.UsedRange 
End Sub

    Failing that you may well have some formatting hanging round. Try clearing/deleting all the cells after your last row.

    Regarding the above also see:

    Excel Developer Tip

    Another method to find the last used cell:

        Dim rLastCell As Range

    Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

    Change the search direction to find the first used cell.