Conditionally unhide worksheets in Excel/vba

Question

I am trying to unhide a group of worksheets if they meet a certain condition. This uses a user form, triggered by a command button, with selection options and another command button. The expected behavior is that once the selection has been made and the button has been pressed, all worksheets meeting the criteria will be unhidden. The Target word is present at different locations along the first row and all cells before it are empty on that row. Ideally, the process will scan each cell in the first row of each worksheet in the workbook until it comes across the Target, unhide the worksheet, then move on to the next worksheet to start the process over again until all worksheets with the workbook have been checked.

Upon activation of the command button on the user form I have the following:

Private Sub ContinueCommand_Click()
Dim Valid As Boolean, wks As Worksheet, c As Integer, actCol As Long    
    actCol = ActiveSheet.Range("A1").End(xlToRight).Column
    For Each wks In ActiveWorkbook.Worksheets
        For c = 1 To actCol
            If ActiveCell.Value = "Target" Then
                wks.Visible = xlSheetVisible
                Exit For
            End If
        Next c
    Next wks
    Valid = True
If Valid = True Then
Unload Me
End If
End Sub

I've borrowed from several sources, including here for using ActiveCell, determining if a value exists, unhidding worksheets, Finding values within a range, and searching for a string. Any help would be greatly appreciated.

Don't use `ActiveCell`. Instead, you likely want something like `If wks.Cells(1, actCol) ...` assuming the "Target" string is in row 1. — BruceWayne, Apr 29 '19 at 18:05
You can replace the IF and inner loop with `wks.Visible = Not IsError(Application.Match("Target",wks.Range("1:1"),0))` — Scott Craner, Apr 29 '19 at 18:09
Also, if as you say all cells before `Target` are empty then the target is going to be either in `wks.Range("A1")` or in `wks.Range("A1").End(xlToRight)`. No need to go through all the cells in `row 1` again. — Stavros Jon, Apr 29 '19 at 18:12
You can move the `actCol=` line down under the next line, unless all worksheets will have the same number of columns, otherwise you will miss some. — Darrell H, Apr 29 '19 at 18:14

score 1 · Answer 1 · answered Apr 30 '19 at 10:14

As I said in my comments there are some issues with the way you've chosen to implement this.

Your For c = 1 To actCol loop is not needed. This can be easily seen because c is not really used anywhere in the loop.

Let's assume your Target value is in wks.Range("A100") (the 1st row and 100th column).

Your code would then perform the exact same operation 100 times and would come up with the exact same result. That's what leads you to use Exit For, which is a bad practice.

If I understood your initial post correctly, if Target exists in a particular worksheet, then all cells before Target are empty.

If that's the case, the Target will either be in wks.Range("A1") or in wks.Range("A1").End(xlToRight). If it's not in either of these two cells then it doesn't exist at all in this particular worksheet, which would mean that the 1st row is completely empty. You don't need to check any more cells apart from these two.

Your code does not check whether Target is in wks.Range("A1").

Also your use of Application.Match, makes me believe that you have probably been misled by the common misconception that wks.Range("A1").End(xlToRight) is a range of cells starting from A1 and extending all the way to the last non-empty cell in the 1st row.

The truth is that wks.Range("A1").End(xlToRight) is a single cell rather than a range of cells. Selecting A1 and then pressing CTRL+right arrow, will show you exactly which cell it is.

I might be missing something, but according to your description in the initial post, I would do something like the following:

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    If sht.Range("A1").Value = "Target" Or sht.Range("A1").End(xlToRight).Value = "Target" Then
        sht.Visible = xlSheetVisible
    Else
        MsgBox "target was not found in " & sht.Name
    End If
Next sht

Bar-Tzur · Accepted Answer · 2019-05-01T14:24:40.900

0

I want to thank BruceWayne, Scott Craner, Stavros Jon, Darell H whom all helped me get closer to this answer. The final result looked like this:

Private Sub ContinueCommand_Click()
Dim wks As Worksheet    
    For Each wks In ActiveWorkbook.Worksheets
       If Not IsError(Application.Match("Target", wks.Range("A1").End(xlToRight), 0)) Then
                wks.Visible = xlSheetVisible
            End If
    Next wks
Unload Me
End Sub

If anyone in the future has issues getting this to work, let me know and I will post a more complete version.

edited May 01 '19 at 14:24

answered Apr 29 '19 at 21:07

Bar-Tzur

85
1
10

1

Your `For c = 1 To actCol` loop is not needed. This can be easily seen because `c` is not really used anywhere in the loop. Let's assume your `Target` value is in the 1st row and 100th column. Your code would then perform the exact same operation 100 times and would come up with the exact same result. **If all cells before 'Target' are empty** then the `Target` will either be in `wks.Range("A1")` or in `wks.Range("A1").End(xlToRight)`. If it's not in either of these two cells then it doesn't exist at all in this particular worksheet, which would mean that the 1st row is completely empty. – Stavros Jon Apr 30 '19 at 09:23
Thanks, @Stavros, I modified my code based on your recommendations. I'm not sure if I was not complete enough in my question but I couldn't get your version to run correctly. – Bar-Tzur Apr 30 '19 at 14:19
Glad I could help. What problems did you have with it? The only problem with your code, now that you've modified it, is that if "BRK" is in `cell A1` , the corresponding worksheet will not be unhidden. This possibility should be taken into account, unless it's impossible for it to happen. You know better. – Stavros Jon Apr 30 '19 at 17:52
I changed `BRK` back to `Target` to eliminate confusion with the rest of this post. That is true for A1 and yes, it is impossible for it to be in that cell and that is the reason I removed it but that is a good call out. The problem was that When I ran your code it returned all worksheets, not just `Target` ones, as though the `if` statement was not there. Tangentially, the `MsgBox` would notify for each page where the condition was not met prompting the message to appear multiple times, which may be related to the first issue. – Bar-Tzur May 01 '19 at 14:32

Conditionally unhide worksheets in Excel/vba

2 Answers2