Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Sheet
On Mon, 16 Apr 2007 22:03:35 -0600, JE McGimpsey
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Sheet
You can use the :
..Cells.value ="" to keep the existing formatting "kirkm" wrote in message ... On Mon, 16 Apr 2007 22:03:35 -0600, JE McGimpsey 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Sheet
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 , kirkm 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Sheet
On Tue, 17 Apr 2007 15:08:57 +1000, "Corey"
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). |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Sheet
On Mon, 16 Apr 2007 23:25:41 -0600, JE McGimpsey
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clearing data from excell sheet | Excel Discussion (Misc queries) | |||
Clearing data from a so called protected sheet??? | Excel Programming | |||
my macro is clearing the wrong sheet | Excel Programming | |||
Clearing old data from a sheet | Excel Programming | |||
Clearing old data from a sheet | Excel Programming |