Macro to remove Rows that are 0, #REF!

[ghp2017]

Hello,

I am trying to create a macro that will actually remove all Rows where cell A1 has a value of 0, #REF !.

 

[mole999]

do you mean delete the sheet / cells if that is the only criteria

 

[ghp2017]

Yes. I am working on an inventory spreadsheet and I want to completely remove and row that has a 0 or displays #REF !

 

[Rick Rothstein]

Yes. I am working on an inventory spreadsheet and I want to completely remove and row that has a 0 or displays #REF !

Are we talking about Column A or any column?

 

[ghp2017]

It's now Column F.

 

[Rick Rothstein]

It's now Column F.

Since some of the cells are displaying #REF ! errors, I presume Column F has formulas in it.

1) Will any of the formulas be displaying the empty text string ("") or will it always be displaying only values (0 or otherwise) or #REF ! errors? If the empty text string is a possible value to display, should it be considered as a 0 and its row deleted or not?

2) When there is an error, is the only error a #REF ! error or could there be others? If there could be others, what should be done with those rows?

 

[ghp2017]

I actually figured out a way to eliminate the #REF ! values. So I just need a macro assigned to a button that will delete any row that has a value of 0 in Column F.

 

[Rick Rothstein]

I actually figured out a way to eliminate the #REF ! values.

Please share it with us.

Also I would still like the answers to my questions.

 

[ghp2017]

1) Will any of the formulas be displaying the empty text string ("") or will it always be displaying only values (0 or otherwise) or [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] ! errors? If the empty text string is a possible value to display, should it be considered as a 0 and its row deleted or not?

A= #REF ! would be considered a 0

2) When there is an error, is the only error a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] ! error or could there be others? If there could be others, what should be done with those rows?

A= #REF ! error is b/c other formulas are not being calculated b/c the cells are not being used (they are extra for non-standard items {ad-hoc})

 

[Rick Rothstein]

1) Will any of the formulas be displaying the empty text string ("") or will it always be displaying only values (0 or otherwise) or [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] [/URL] ! errors? If the empty text string is a possible value to display, should it be considered as a 0 and its row deleted or not?

A= [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] ! would be considered a 0

2) When there is an error, is the only error a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] [/URL] ! error or could there be others? If there could be others, what should be done with those rows?

A= [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] ! error is b/c other formulas are not being calculated b/c the cells are not being used (they are extra for non-standard items {ad-hoc})

You did not actually answer the questions I asked...

First off, you did not tell us the method you came up with to handle #REF ! errors.

Second, I asked if "" was a possible outcome for your formula and, if so, should it be considered as 0.

Third, I asked if other errors besides #REF ! are possible (for example #VALUE !, #N/A, etc.) and, if so, what should be done with them.