VBA to Auto Hide Rows with N/A as Value

[Bkisley]

Hey guys! I have a macro created that is near perfect for what I need. The only downfall is that it leaves the 50 rows the macro deals with unhidden. I need to add to my macro to hide all rows with rows 56 and 105 that have a value of N/A in column A.

Example - cells A56:A66 all have a percent in the cell. Cells A67:A105 have N/A in the cell. When I click my macro button, I need to automatically hide rows A67:105. If I then add to my data and now A56:A75 now have a percent in the cell, I would need my macro (when clicked for the second time) to leave rows A56:A75 unhidden and hide A76:A105

What do I need to do??


Thanks!

 

[Roderick_E]

Will the N/A always occur in column A?

 

[Bkisley]

Will the N/A always occur in column A?

in column A I have an If formula. If it is true it returns a number if it is false "N/A"

 

[Fluff]

How about

Sub HideNA()
   Range("A56:A105").SpecialCells(xlFormulas, xlErrors).EntireRow.Hidden = True
End Sub

 

[Bkisley]

I guess I should have explained this better, the formula I have does not return an error N/A but rather me literally typing N/A. I have an if statement and when it is false, the end of my equation looks like --> ,"N/A")

 

[Fluff]

Ok, what code do you already have?

 

[Bkisley]

@Fluff

Sub OnePercentDown()
'
' OnePercentDown Macro
'


'
    Sheets("Financial Model").Select
    Range("F56:F105").Select
    Selection.Cut
    ActiveWindow.SmallScroll ToRight:=3
    Range("Y56").Select
    ActiveSheet.Paste
    Range("F56").Select
    ActiveCell.FormulaR1C1 = "=RC[19]*(1-1%)"
    Range("F56").Select
    Selection.AutoFill Destination:=Range("F56:F105"), Type:=xlFillDefault
    Range("F56:F105").Select
    Selection.NumberFormat = "$#,##0.000"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("Y56:Y105").Select
    Sheets("Financial Summary - System").Select
    Range("E13").Select
    Selection.Copy
    Range("E22").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E15:E16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Financial Model").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("F56").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
[B][COLOR=#ff0000]THIS IS WHERE I NEED THE HIDE COMMAND[/COLOR][/B]
    Sheets("Financial Summary - System").Select
    Range("C25").Select
End Sub

 

[Fluff]

First off, when posting code, please use the code tags (the # icon in the reply window).

Secondly try

    For Each Cl In Range("A56:A105")
      If Cl.Value = "N/A" Then
         Cl.EntireRow.Hidden = True
      Else
         Cl.EntireRow.Hidden = False
      End If
   Next Cl

 

[Bkisley]

Will do - This works great, it is a little slow though, but it works great!

 

[Fluff]

If you are happy to change your formula so that, rather returning "N/A" it returns FALSE (no quotes) you could use

Range("A56:A105").SpecialCells(xlFormulas, xlLogical).EntireRow.Hidden = True