joelle
02-26-2008, 09:56 AM
Dear Experts,
My macro below will clear any quantity in column I but it will leave formula there if any, and it been working fine with all Excel versions before Vista.
Now, with Vista, it clears all qty's in col I and goes over to col H next door and clear all the "$I$4" I have in col H. Thats so outrageous. Why is Vista that different and is there a way to beat that? Many thanks, pls help me out.
----------------------------------------
Sub clear_col_I()
Dim ws As Worksheet, rngToClear As Range
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case 'worksheetA"
On Error Resume Next
Set rngToClear = ws.Range("i:i").SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rngToClear Is Nothing Then rngToClear.ClearContents
Case Else
'do nothing with any other sheets
End Select
Next ws
End Sub
------------------------------------
My macro below will clear any quantity in column I but it will leave formula there if any, and it been working fine with all Excel versions before Vista.
Now, with Vista, it clears all qty's in col I and goes over to col H next door and clear all the "$I$4" I have in col H. Thats so outrageous. Why is Vista that different and is there a way to beat that? Many thanks, pls help me out.
----------------------------------------
Sub clear_col_I()
Dim ws As Worksheet, rngToClear As Range
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case 'worksheetA"
On Error Resume Next
Set rngToClear = ws.Range("i:i").SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rngToClear Is Nothing Then rngToClear.ClearContents
Case Else
'do nothing with any other sheets
End Select
Next ws
End Sub
------------------------------------