Getting the actual usedrange

Question

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?

Answer 1

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.

Answer 2

Readify made a very complete answer. Yet, I wanted to add the End statement, you can use:

Find the last used cell, before a blank in a Column:

Sub LastCellBeforeBlankInColumn() Range("A1").End(xldown).Select End Sub 

Find the very last used cell in a Column:

Sub LastCellInColumn() Range("A" & Rows.Count).End(xlup).Select End Sub 

Find the last cell, before a blank in a Row:

Sub LastCellBeforeBlankInRow() Range("A1").End(xlToRight).Select End Sub 

Find the very last used cell in a Row:

Sub LastCellInRow() Range("IV1").End(xlToLeft).Select End Sub 

See here for more information (and the explanation why xlCellTypeLastCell is not very reliable).