• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Personal macro that Hides rows based on criteria in column A

Sadhna

New Member
I have 4 workbooks. Column A of all 4 workbooks has a calculation which determines if the row should be hidden or not.
Each workbook has about 40 sheets.

I would like to record a personal macro that is able to run for all 4 workbooks so that I can hide the rows which need to be hidden. I would also like an unhide option, if possible.
 

Sadhna

New Member
Apologies in advance and please excuse my stupidity. I am failing dismally at trying to do this.
What does designation mean?
I have attached the file. The other 3 files have a similar layout in that column A will either resolve as Show or Hide.
 

Attachments

AlanSidman

Active Member
Code:
Option Explicit

Sub HideRows()
    Dim ws As Worksheet, sh As Worksheet
    Set sh = Sheets("Index")
    Dim i As Long, lr As Long
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "INDEX" Then
            lr = ws.Range("B" & Rows.Count).End(xlUp).Row
            For i = 10 To lr
                If ws.Range("A" & i) = "Hide" Then
                    ws.Range("A" & i).EntireRow.Hidden = True
                End If
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True
    MsgBox "Completed"

End Sub
Code:
Option Explicit

Sub Unhide()
    Dim ws As Worksheet, sh As Worksheet
    Set sh = Sheets("Index")
    Dim i As Long, lr As Long
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "INDEX" Then
            lr = ws.Range("B" & Rows.Count).End(xlUp).Row
            For i = 10 To lr
                If ws.Range("A" & i).EntireRow.Hidden = True Then
                    ws.Range("A" & i).EntireRow.Hidden = False
                End If
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True
    MsgBox "Completed"
End Sub
 

Sadhna

New Member
Thank you for this.

I created a new excel work book, clicked on the developer tab, clicked on record macro and named and saved this file as Personal.xlsb
Then I opened the personal workbook again and clicked on the developer tab, VBA project and selected module 1 and pasted the code here.

I then opened the workbook which I sent to you and tried to execute the code but this did not work. What am I doing wrong now?
 

AlanSidman

Active Member
It worked when I put it directly in the workbook you provided. Cannot determine what you did to not let it work. Did you enable macros in your profile? Have you tried running it from the wkbook you provided?
 
Top