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(xlCellTypeLastCell)
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

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 <(E-Mail Removed)>,
kirkm <(E-Mail Removed)> 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(xlCellTypeLastCell)
> 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

On Mon, 16 Apr 2007 22:03:35 -0600, JE McGimpsey
<(E-Mail Removed)> wrote:

>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
>

Thank you. That did work but (as you say) it
loses formatting. I'd like to keep that, if possible.

Should I use a different command, or re-apply formatting?

Thanks - Kirk

You can use the :
..Cells.value =""
to keep the existing formatting

"kirkm" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
On Mon, 16 Apr 2007 22:03:35 -0600, JE McGimpsey
<(E-Mail Removed)> wrote:

>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
>

Thank you. That did work but (as you say) it
loses formatting. I'd like to keep that, if possible.

Should I use a different command, or re-apply formatting?

Thanks - Kirk

If you keep the formatting by using .ClearContents, then the UsedRange
will reflect the formatted cells.

But if you're using .Cells.ClearContents, then rng can be set to cell A1
and lastRow will always = 1, right?



In article <(E-Mail Removed)>,
kirkm <(E-Mail Removed)> wrote:

> Thank you. That did work but (as you say) it
> loses formatting. I'd like to keep that, if possible.
>
> Should I use a different command, or re-apply formatting?

On Tue, 17 Apr 2007 15:08:57 +1000, "Corey" <(E-Mail Removed)>
wrote:

>You can use the :
>.Cells.value =""
>to keep the existing formatting

That worked, but also seems to keep the previous count
(which I want to lose).

On Mon, 16 Apr 2007 23:25:41 -0600, JE McGimpsey
<(E-Mail Removed)> wrote:

>If you keep the formatting by using .ClearContents, then the UsedRange
>will reflect the formatted cells.
>
>But if you're using .Cells.ClearContents, then rng can be set to cell A1
>and lastRow will always = 1, right?

Gosh, now i'm losing the plot

Yes, everytime the Sheet is cleared it should be 1. (Not zero?).

So, if I set the range at that point, as you suggest, my lastRow
variable will be 1 ?

I'm fairly new to all this... will have a play.

Thanks - Kirk