Hide UnHide Macros Excel VBA

Question

I am trying to make two macros: one that hides rows based on a criterion in a certain column, and another that unhides rows based on the same criterion.

Here is the code for the first macro, which works fine:

Sub hide()
    Dim item As Variant

    For Each item In Range("f:f")
        If item = "complete" Then item.EntireRow.hidden = True
    Next
End Sub

It's the second macro, the unhiding one, that doesn't work for me. Excel tells me it cannot run the macro or all macros have been disabled. Here is the code for the second macro:

Sub Unhide()
    Dim item As Variant

    For Each item In Range("f:f")
        If item = "complete" Then
            item.EntireRow.hidden = False
    Next
End Sub

If it's relevant, each macro is located in its own module.

Would anything here help you? blog.contextures.com/archives/2009/01/12/hide-an-excel-macro — ryyker
– ryyker, Commented Jun 25, 2014 at 20:30

Floris · Accepted Answer · 2014-06-25 20:48:13Z

1

In your second macro you have a missing End If:

Sub Unhide()
    Dim item As Variant

    For Each item In Range("f:f")
        If item = "complete" Then
            item.EntireRow.hidden = False
        End If   ' <<<<<<<<<<<<< missing
    Next
End Sub

Add that, and you're good to go.

The difference between the first and the second Sub is this: in the first, you put the statement right after the Then. In the second, you go to a new line. Unlike in C, you need to indicate with an End If where the group of statements ends.

Normally you don't just get a "can't run code" error, but an actual Next without For type of error (that's what happened when I pasted your code and tried to compile it).

In future, click Debug->Compile VBA Project before trying to run your code, and heed the error messages. It can be quite a time saver.

answered Jun 25, 2014 at 20:48

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Balhoff · Accepted Answer · 2014-06-25 20:43:45Z

0

Why not just step through all the used rows in the spreadsheet and check the value of Column F?

Sub hide()
    Dim i As Integer
    For i = 1 to ActiveSheet.UsedRange.Rows.Count
        If ActiveSheet.Cells(i, 6).Value = "complete" Then
            ActiveSheet.Rows(i).EntireRow.hidden = True
        End If
    Next
End Sub

Further it would probably be cleaner to just combine the hide and unhide...

Sub hide(ByVal isHide As Boolean)
    Dim i As Integer
    For i = 1 to ActiveSheet.UsedRange.Rows.Count
        If ActiveSheet.Cells(i, 6).Value = "complete" Then
            ActiveSheet.Rows(i).EntireRow.hidden = isHide
        End If
    Next
End Sub

answered Jun 25, 2014 at 20:43

Mark Balhoff

2,3644 gold badges24 silver badges31 bronze badges

Comments

xiaomy · Accepted Answer · 2014-06-25 20:47:29Z

0

Just tested - The code is fine. You do need an End If in your Unhide procedure though, right before Next.

answered Jun 25, 2014 at 20:47

xiaomy

1214 bronze badges

Collectives™ on Stack Overflow

Hide UnHide Macros Excel VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related