Hide rows in excel that return #N/A 1

[RyanScharfy]

I have a list in excel. I'd like to automatically hide the rows that return "N/A#" in column C from row 2 to 40.

The following code is close.. it does it if the values of column C are zero.

Note: I do not want to get rid of the formulas in row C and I do not want to use the filter functionality for ascetic format reasons for the end users.

Sub RwHide()
Dim RwCnt As Integer
For RwCnt = 2 To 40
If Range("C" & RwCnt) = 0 Then
Range("C" & RwCnt).EntireRow.Hidden = True
End If
Next
End Sub

 

[SkipVought]

Hi,

Turn on the AutoFilter and filter accordingly. Use your macro recorder if so desired.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[RyanScharfy]

Hi Skip,

I don't want the AutoFilter arrows to show up on the workbook.

 

[SkipVought]

WHY? It is a GREAT feature of Excel! Controls ought to be an integral part of moderate to complex spreadsheet designs.

So hide the row with the buttons, and reference in a row above if so desired.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[TheCloak]

please use code tags... makes it easier

Sub RwHide()
Dim RwCnt As Integer
For RwCnt = 2 To 40
    If Range("C" & RwCnt) = 0 Then
        Range("C" & RwCnt).EntireRow.Hidden = True
    End If
Next
End Sub

what about something with the height?

Dim RwCnt as integer
For RwCnt - 2 to 40
    If Range("c" & RwCnt) = 0 then
         Rows("c" & RwCnt).RowHeight = 0
    End If
Next RwCnt

I am not sure if that is the right format of the Rows() might want to look into it.


--
-TheCloak

"You Never Know What Hits You, A Gunshot is the Perfect Way" - JFK

 

[RyanScharfy]

Hi Skip,

Thanks for the help, great idea on the row hiding, but I've run into another roadblock. Using auto filter doesn't work because in my worksheet I have to filter two different tables on the same worksheet which Excel doesn't allow.

My original question/example was extremely simplified. The code I wrote in my original request would work fine, but for the "N/A!" formula/integer issue. That's really what I'm trying to get answered. I've been googling all night with no luck. I wish I never gave my VBA book away a couple years back!

 

[RyanScharfy]

Hi Cloak,

The issue isn't how to hide the rows, the issue is getting the code to recognize the N/A cells that the formula returns. My example uses zeros and integers. I'm trying to get it to recognize formula values and N/A! results. Thanks for the tip, though.

 

[PHV]

what about this ?

Sub RwHide()
Dim RwCnt As Integer
For RwCnt = 2 To 40
  If Application.IsNA("C" & RwCnt) Then
    Rows(RwCnt).Hidden = True
  End If
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"...two different tables on the same worksheet..."

Not a good workbook design. ONE table per sheet.

Here's the rub:
You want to HIDE rows with NA. You have two tables. Are they side by side? Hiding NA in one table may hide data in the other table that you do not want to hide.



Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[RyanScharfy]

Hi Skip,

It's a great report! If I can get it to work. Each table is one below the other. If it was just one report I wouldn't want a macro, but my workbook has 90+ clients (one client per worksheet, with charts, etc). Each client returns a unique number of rows. My solution is to set the tables the same lenghth on every worksheet, and just hide the rows where data doesn't come in (i.e. - I get a N/A# returned on the vlookups). The source data are in "data dump" hidden worksheets.

I'm thinking my solution might be add in a macro that adds in the formulas, copy and paste special values, change #N/A to zeros, and run a version of the macro above.

 

[RyanScharfy]

Hi PHV,

Hm, interesting code, but it doesn't actually hide any rows.

 

[SkipVought]

"one client per worksheet"

AHHHhhhhhhhhhhhccccchhhhh!!!

What a mess! This workbook design Soooooooo complicates your efforts.

"Each table is one below the other"

That is NOT the way data ought to be STORED for analysis and reporting. REALLY a bad design. Tables on different sheets can easily be JOINED and REPORTED on another sheet to accolplish your NA requirement in TOTAL!

Your workbook design leaves ALOT to be desired. It severely limits the functionality of Excel. You have visualized how the data is to be reported as the basis for your design. STORAGE and REPORTING formats are different animals. Similar data ought to be stored in a SINGLE table. REPORTING can be for separate clients on separate sheets if so desired, much MUCH easier than working with stored data on multiple sheets/tables.

You really ought to seriously rething your design!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[RyanScharfy]

Some of us live in the real world Skip, where co-workers and wants and needs.

 

[SkipVought]

That's okay. You'll have a job trying to keep this together, until someone else comes along and takes the time and effort to fix it. Been there. Done that!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[TheCloak]

you guys are ridiculous if you aren't going to help whats the point in complaining. THATS TO YOU AND TO SKIP quit being babies and help each other!

--
-TheCloak

"You Never Know What Hits You, A Gunshot is the Perfect Way" - JFK

 

[SkipVought]

TC,

You already posted a satisfactory solution for the OP. Had you not, I would have, as I often do.

I am simply pointing out that there is a much better way, than just assuming that the poor OP ought to stumble around with the current solution as the best that can be.

I deal with this kind of stuff on a regular basis, in a company that is a major aircraft manufacturer. People do not know any better, and consequently produce "solutions" that are very difficult to work with. I attempt to educate, in order to avoid this kind of waste.

If you have not figured it out by now, it is a sort of mission. I was there many years ago, but I've been converted, and thanks to members at Tek-Tips! ;-)

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[SkipVought]

Actually, continuing to think about your task, you still could use the AutoFilter approch by selecting the COLUMNS that your two tables occupy, and, assuming that the NA's are in the same column, just turn on the AutoFilter and assign your criteria.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[RyanScharfy]

BINGO! Thanks Skip. That DOES work. Nice and easy.