UsedRange will continue to include formatted cells. If you don't want to
preserve formatting, then use .Clear rather than .ClearContents. Some
versions of XL require you to use .UsedRange to reset the used range.

Dim rng As Range
Dim lastRow As Long
With Worksheets("Reports")
.Cells.Clear
.UsedRange
Set rng = .Cells.SpecialCells(xlCellTypeLastCell)
lastRow = rng.Row
End With
MsgBox rng.Address & vbNewLine & lastRow

In article ,
kirkm wrote:

Hi,

I have a bit of code e.g.

Dim wk As Workbook
Set wk = ActiveWorkbook
Dim sht As Worksheet
Set sht = wk.Worksheets("Reports")

With sht
.Cells.ClearContents
End With

This clears the sheet, as required, BUT if I add this next:

With Worksheets("Reports")
Set rng =
Worksheets("Reports").Range("A1").SpecialCells(xlC ellTypeLastCell)
LastRow = rng.Row
End With

LastRow still contains the number of lines in the just cleared sheet.

Can I do something to make LastRow variable correct?

Thanks - Kirk