Clear Numeric and Blank Cells (Excel 2003)

Topic

New Reply

I am trying to clear the contents of cells in columns A to M when the corresponding row in Column A is blank or numeric
The code I have is:

Sub Row_Clear2()
Dim a As Long
Dim b As Long
Dim LClearCells As String
‘data will cleared in columns A to M when Column A is blank or numeric
LClearCells = “A” & a & “:” & “M” & a

a = Range(“A300”).End(xlUp).Row
For b = a To 2 Step -1
If IsNumeric(Range(“A” & ) Or Range(“A” & = “” Then
Range(LClearCells).ClearContents
End If
Next b
End Sub

The code fails at “Range(LClearCells).ClearContents”
Can you help?

Reply | Quote

Replies

You assign a value to LClearCells outside of the loop, even before a has been assigned a value. You should assign LClearCells inside the loop and use the loop index b instead of the loop limit a:

Sub Row_Clear2()
Dim a As Long
Dim b As Long
Dim LClearCells As String

a = Range("A300").End(xlUp).Row
For b = a To 2 Step -1
If IsNumeric(Range("A" & ) Or Range("A" & = "" Then
'data will be cleared in columns A to M when Column A is blank or numeric
LClearCells = "A" & b & ":M" & b
Range(LClearCells).ClearContents
End If
Next b
End Sub

Reply | Quote

Thanks Hans .I’m just not thinking today

Reply | Quote