How do I build "hide a row" into a macro if #N/A appears in a colu
 

I have several large workbooks in Excel with up to 28 sheets. I use a
VLOOKUP to enter pricing in the sheets, some 10,000. I have 50 offices with
different product availability factors. If the product is unavailable a #N/A
appears. I then go in and hide the row or rows where #N/A appears in the
pricing column. There could be up to 150 rows that need to be manually
hidden per sheet. Can this search and hide function be accomplished by
building a macro which would search the entire workbook or even a single
sheet to hide the rows which reflect the #N/A in a specific column

How do I build "hide a row" into a macro if #N/A appears in a colu
 

select the pricing column do
Edit=Goto =Special Cells

Select formulas and Errors
then Click OK.

No do Format=Rows=Hide

in code

Sub HideRows()
Dim sh as Worksheet
Dim rng as Range
for each sh in Worksheets
sh.Rows.Hidden = False
On error resume next
set rng = sh.Columns(3).SpecialCells(xlFormulas,xlErrors)
On error goto 0
if not rng is nothing then
rng.EntireRow.Hidden = True
end if
Next
end sub



change the 3 in Columns(3) to reflect the pricing column.
--
Regards,
Tom Ogilvy

"Dick" wrote:

I have several large workbooks in Excel with up to 28 sheets. I use a
VLOOKUP to enter pricing in the sheets, some 10,000. I have 50 offices with
different product availability factors. If the product is unavailable a #N/A
appears. I then go in and hide the row or rows where #N/A appears in the
pricing column. There could be up to 150 rows that need to be manually
hidden per sheet. Can this search and hide function be accomplished by
building a macro which would search the entire workbook or even a single
sheet to hide the rows which reflect the #N/A in a specific column

How do I build "hide a row" into a macro if #N/A appears in a colu
 

How about autofilter? Select Custom, Does Not Contain = #N/A

Charles
Dick wrote:
I have several large workbooks in Excel with up to 28 sheets. I use a
VLOOKUP to enter pricing in the sheets, some 10,000. I have 50 offices with
different product availability factors. If the product is unavailable a #N/A
appears. I then go in and hide the row or rows where #N/A appears in the
pricing column. There could be up to 150 rows that need to be manually
hidden per sheet. Can this search and hide function be accomplished by
building a macro which would search the entire workbook or even a single
sheet to hide the rows which reflect the #N/A in a specific column