How to limit worksheet extremities ?

[shumba]

Good Morning,


I have a worksheet with values in the range A1:I3006. When I check the extremities of the worksheet (by pressing Cntl + End) the cursor moves to the cell at OYL10754.


Please will someone show me a way of limiting the extremities to the current range with values.


I have tried the usual Clear and ClearContents methods as in the procedure below but to no avail.

Sub Clear_contents()
      Worksheets("Data Collection and Compilation").Range("k1:oyl10754").Clear
  End Sub

Any help will be much appreciated.

Rob.

 

[JoeMo]

Try this on a copy of your worksheet:

Sub RemoveFormatOutsideUsedRange()
Dim ws As Worksheet, myCell As Range
For Each ws In ActiveWorkbook.Worksheets
    Set myCell = lastCell(ws)
    Range(myCell.Columns(1).Offset(0, 1), myCell.Columns(1).Offset(0, 1).End(xlToRight)) _
    .EntireColumn.Clear
    Range(myCell.Rows(1).Offset(1, 0), myCell.Rows(1).Offset(1, 0).End(xlDown)).EntireRow.Clear
Next ws
End Sub
Function lastCell(ws As Worksheet) As Range
'Finds last cell in Used Range (i.e., the intersection of last filled cell
'in a row with last filled cell in a column
Dim LastRow&, LastCol%
' Error-handling is here in case there is not any
' data in the worksheet
On Error Resume Next
With ws
' Find the last real row
    LastRow& = .Cells.Find(what:="*", _
      searchdirection:=xlPrevious, _
      searchorder:=xlByRows).Row
  ' Find the last real column
    LastCol% = .Cells.Find(what:="*", _
      searchdirection:=xlPrevious, _
      searchorder:=xlByColumns).Column
  End With
If Err.Number <> 0 Then 'worksheet is empty
   MsgBox ("Worksheet " & ws.Name & " is empty (all cells are blank).")
   Set lastCell = ws.Cells(1, 1)
Else
  Set lastCell = ws.Cells(LastRow&, LastCol%)
End If
End Function

If that doesn't work, try changing .clear to .delete in the sub.

 

[shumba]

Thanks for your help.

Regards,

Rob.