Related:
- Macro to find coloured cells and remove #N/A
- How to remove a number from blacklist - Guide
- Ubuntu remove password - Guide
- Remove recent files windows 11 - Guide
- Code to remove number from blacklist - Guide
- Spell number in excel without macro - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 16, 2009 at 09:48 PM
Sep 16, 2009 at 09:48 PM
question 1
try this macro (modify to suit you)
question no. 2
can I give a solution without a macro
============
control+G
choose "special" at the bottom left
in the "goto special" window
choose the radio button "constants"
uncheck the button
numbers, text and logicals
keep tick only in errors
click ok
now all the cells with error(#N/A) will be highlightd
without selecting any other cell
click edit(menu)-clear-clearall
============================
do you get what you want.
this will remove other error cells also like"value" or "#DIV/0!" etc.
if your really want a macro then RECORD a macro taking the above steps and suitably modify the macro.
try this macro (modify to suit you)
Sub test()
Dim rng As Range, c As Range, j As Integer
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In rng
If c.Interior.ColorIndex <> xlNone Then
j = c.Interior.ColorIndex
c.EntireRow.Interior.ColorIndex = j
End If
Next c
End Sub
question no. 2
can I give a solution without a macro
============
control+G
choose "special" at the bottom left
in the "goto special" window
choose the radio button "constants"
uncheck the button
numbers, text and logicals
keep tick only in errors
click ok
now all the cells with error(#N/A) will be highlightd
without selecting any other cell
click edit(menu)-clear-clearall
============================
do you get what you want.
this will remove other error cells also like"value" or "#DIV/0!" etc.
if your really want a macro then RECORD a macro taking the above steps and suitably modify the macro.
Hi, Thanks.
The first color macro worked great, but the second suggestion not so. As this function selects all the errors within the entire data range, I'm only wanting to select any errors that occur outside the data it self. I'll explain, the data range is not constant, Column "B" is the most important piece of information. I have other columns which run longer than coloum "B", but if there is no data in "A" or "B" then a error will occur in other columns, so i need to delete these errors, but i do not want to delete any errors that may occur in the effective data range.
I hope this helps.
The first color macro worked great, but the second suggestion not so. As this function selects all the errors within the entire data range, I'm only wanting to select any errors that occur outside the data it self. I'll explain, the data range is not constant, Column "B" is the most important piece of information. I have other columns which run longer than coloum "B", but if there is no data in "A" or "B" then a error will occur in other columns, so i need to delete these errors, but i do not want to delete any errors that may occur in the effective data range.
I hope this helps.
Just use IF function.
If column A = "" then "" else if column B= "" then "" else TheFormulaYourUsing.
IF(A1="","",IF(B1="","",YourFormula)).
Best regards,
Trowa
If column A = "" then "" else if column B= "" then "" else TheFormulaYourUsing.
IF(A1="","",IF(B1="","",YourFormula)).
Best regards,
Trowa
