Hide Columns and Rows

[mathsbeauty]

Hello! I am new here. I have two macros.
1. This Macro Hides all columns whose first entry is "N" and shows all columns whose first entry is "Y".
2. The another Macro unhides all columns i.e. entire data is shown.

I need to modify these two macros to the following (or create new ones)
1. The first macro should hide all columns whose first entry is "N" and shows all columns whose first entry is Y and hides those rows which contains "NO" in this column (whose first entry is Y).
2. This macro should unhide all rows and columns i.e. entire data is shown.



The Macro code is:

Option Explicit

Sub hideCols()
Dim cl As Range
Dim rng As Range
With Worksheets(1)
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight))
For Each cl In rng
If cl.Text = "N" Then cl.EntireColumn.Hidden = True
Next cl
End With
End Sub
Sub showCols()
Dim col As Long
Dim cnt As Long

cnt = Worksheets(1).UsedRange.Columns.Count

For col = 1 To cnt
If Columns(col).EntireColumn.Hidden = True Then Columns(col).EntireColumn.Hidden = False
Next
End Sub


I hope to get some help from experts. I will be really thankful if I get some help.

 

[VoG]

Perhaps

Sub hideCols()
Dim LR As Long, LC As Long, i As Long, j As Long
With Sheets(1)
    LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LC = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    For j = 1 To LC
        .Columns(j).Hidden = .Cells(1, j).Value = "N"
        If .Cells(1, j).Value = "Y" Then
            For i = 2 To LR
                .Rows(i).Hidden = .Cells(i, j).Value = "NO"
            Next i
        End If
    Next j
End With
End Sub


Sub ShowCols()
Dim LR As Long, LC As Long, i As Long, j As Long
With Sheets(1)
    LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LC = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    For j = 1 To LC
        .Columns(j).Hidden = False
        For i = 2 To LR
            .Rows(i).Hidden = False
        Next i
    Next j
End With
End Sub

 

[mathsbeauty]

Thanks Dear! It works perfectly! You are great. You understood my complex problem easily and solved immediately. GREAT!!!. Thanks Thanks and Thanks for your help.

 

[Peter_SSs]

Welcome to the MrExcel board!

If I have understood correctly, you should be able to avoid a lot of the looping through worksheet cells. In a copy of your workbook, unhide all your columns/rows then try these.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> HideColsRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets(1).Range("A1").CurrentRegion.EntireColumn<br>        <SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> .Columns.Count<br>            <SPAN style="color:#00007F">If</SPAN> .Cells(1, c).Value = "N" <SPAN style="color:#00007F">Then</SPAN><br>                .Columns(c).Hidden = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                .AutoFilter Field:=c, Criteria1:="<>NO"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> ShowColsRows()<br>    <SPAN style="color:#00007F">With</SPAN> Sheets(1).Range("A1").CurrentRegion<br>        .Parent.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>        .EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mathsbeauty]

THANKS Peter_SSs!!! It works beautifully. I appreciate your intelligently constructed macro.