Macro to hide / un-hide #/NA

[cuda8630]

I have a summary tab that is populated by Vlookups off of the other tabs in the workbook.

I would like to create a sort of "refresh" macro that will allow the user to click the button which would then unhide all cells and then hide only cells that contain a #N/A returned from the vlookup in column D after updates are made on the other tabs.

Any ideas would be helpful...

Thanks!

 

[Jonmo1]

Welcome to the board.

Try this

Sub Macro1()
Columns("B:B").EntireRow.Hidden = False
On Error Resume Next
Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Hidden = True
On Error GoTo 0
End Sub

 

[Rick Rothstein]

Try this

Sub Macro1()
Columns("B:B").EntireRow.Hidden = False
On Error Resume Next
Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Hidden = True
On Error GoTo 0
End Sub

Why not use the defined VB constant in place of the "magic number" 16 (I think if would be more self-documenting)?

Columns("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True

 

[Jonmo1]

Why not use the defined VB constant in place of the "magic number" 16 (I think if would be more self-documenting)?

Columns("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True

Because it came from the Macro Recorder.

 

[Rick Rothstein]

Because it came from the Macro Recorder.

Ah, that explains it! I have seen others do the same thing and wondered why they would go through the trouble of converting the constant to a numerical value instead of just using the built-in constant directly, especially given that the help files do not even list the numerical equivalents for those constants. I never gave a thought to what the Macro Recorder might be doing if asked to generate the code line.

 

[Jonmo1]

Ah, that explains it! I have seen others do the same thing and wondered why they would go through the trouble of converting the constant to a numerical value instead of just using the built-in constant directly, especially given that the help files do not even list the numerical equivalents for those constants. I never gave a thought to what the Macro Recorder might be doing if asked to generate the code line.

The macro recorder is such a great tool, and far too often overlooked.
If I don't know the syntax off the top of my head, that is one of my first steps.

 

[Rick Rothstein]

The macro recorder is such a great tool, and far too often overlooked.
If I don't know the syntax off the top of my head, that is one of my first steps.

Interesting how different people handle the same situation differently... whenever I am stuck for syntax, I type the keyword somewhere in the VB editor (if it is not already typed in somewhere) and press F1 to get the help files for that keyword and then build the code line from that. I should mention, though, that I always keep an instance of XL2003 loaded for that purpose (as well as to test my code/formulas across multiple versions of Excel) because I find the help files in XL2007 less than useful (XL2010 is a little bit better in that respect, but XL2003's help files are located in the computer so it doesn't have to load a web page like XL2007 and above do).

 

[cuda8630]

Interesting how different people handle the same situation differently... whenever I am stuck for syntax, I type the keyword somewhere in the VB editor (if it is not already typed in somewhere) and press F1 to get the help files for that keyword and then build the code line from that. I should mention, though, that I always keep an instance of XL2003 loaded for that purpose (as well as to test my code/formulas across multiple versions of Excel) because I find the help files in XL2007 less than useful (XL2010 is a little bit better in that respect, but XL2003's help files are located in the computer so it doesn't have to load a web page like XL2007 and above do).

Thanks for the help...the code works great with the added button for the user to click...

 

[Jonmo1]

Glad to help, thanks for the feedback.