HideUnhide Rows or Columns With Excel VBA

Objective:

We will write a VBA code that hides and unhides rows or columns based on specific criteria (such as a specific row/column number or based on certain conditions). The code will include examples of how to:

• Hide specific rows or columns.
• Unhide specific rows or columns.
• Use a dynamic approach where the code identifies which rows or columns to hide/unhide.

Excel VBA Code Example:

Step 1: Open the VBA Editor

Press Alt + F11 to open the VBA editor in Excel.

Step 2: Insert a Module

In the VBA editor, click on Insert and then Module to insert a new module.

Step 3: Add the VBA Code

Here is the VBA code to hide and unhide rows and columns:

Sub HideUnhideRowsColumns()
    ' Declare variables
    Dim ws As Worksheet
    Dim rowStart As Long, rowEnd As Long
    Dim colStart As Long, colEnd As Long   
    ' Set the worksheet reference (active sheet in this case)
    Set ws = ThisWorkbook.ActiveSheet 
    ' Hiding rows 3 to 5
    rowStart = 3
    rowEnd = 5
    ws.Rows(rowStart & ":" & rowEnd).Hidden = True
    Debug.Print "Rows " & rowStart & " to " & rowEnd & " are hidden."   
    ' Unhiding rows 3 to 5
    ws.Rows(rowStart & ":" & rowEnd).Hidden = False
    Debug.Print "Rows " & rowStart & " to " & rowEnd & " are unhidden."   
    ' Hiding columns B to D (2nd to 4th columns)
    colStart = 2
    colEnd = 4
    ws.Columns(colStart & ":" & colEnd).Hidden = True
    Debug.Print "Columns " & colStart & " to " & colEnd & " are hidden."  
    ' Unhiding columns B to D
    ws.Columns(colStart & ":" & colEnd).Hidden = False
    Debug.Print "Columns " & colStart & " to " & colEnd & " are unhidden."
End Sub

Detailed Explanation:

Let’s break down the code part by part:

1. Declaring Variables:

• Dim ws As Worksheet
  Dim rowStart As Long, rowEnd As Long
  Dim colStart As Long, colEnd As Long

  ws is declared as a variable to represent a specific worksheet (this allows us to perform operations on a specific sheet).

• rowStart, rowEnd, colStart, and colEnd are declared as Long type variables. These represent the start and end points for rows and columns you want to hide or unhide.

2. Setting the Worksheet:

Set ws = ThisWorkbook.ActiveSheet

• ThisWorkbook refers to the workbook where the code is being executed.
• ActiveSheet refers to the currently active sheet in the workbook.

3. Hiding Rows:

ws.Rows(rowStart & ":" & rowEnd).Hidden = True

• ws.Rows(rowStart & « : » & rowEnd) refers to a range of rows starting from rowStart to rowEnd.
• The Hidden = True part hides those rows.

For example, if rowStart = 3 and rowEnd = 5, this will hide rows 3, 4, and 5.

4. Unhiding Rows:

ws.Rows(rowStart & ":" & rowEnd).Hidden = False

• This line will unhide the rows from rowStart to rowEnd. It does this by setting the Hidden property to False.

5. Hiding Columns:

ws.Columns(colStart & ":" & colEnd).Hidden = True

• ws.Columns(colStart & « : » & colEnd) refers to a range of columns starting from colStart to colEnd.
• The Hidden = True hides those columns.

For example, if colStart = 2 and colEnd = 4, this will hide columns B, C, and D.

6. Unhiding Columns:

ws.Columns(colStart & ":" & colEnd).Hidden = False

This line unhides the columns from colStart to colEnd by setting the Hidden property to False.

7. Debugging Output:

Debug.Print "Rows " & rowStart & " to " & rowEnd & " are hidden."

• The Debug.Print statement will print messages in the Immediate Window of the VBA editor, helping to verify the actions that have taken place (e.g., whether the rows or columns were hidden or unhidden).

Running the Code:

Once you’ve inserted the code into the module, you can run the HideUnhideRowsColumns macro by pressing F5 while in the VBA editor or by assigning it to a button or event in Excel.

Additional Use Cases:

1. Hiding based on a condition: You can also use conditions to dynamically hide/unhide rows or columns based on cell values. Here’s an example where we hide rows if the value in column A is less than 5:

Sub HideRowsBasedOnCondition()
    Dim ws As Worksheet
    Dim row As Long
    Set ws = ThisWorkbook.ActiveSheet
    ' Loop through each row in the worksheet
    For row = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        If ws.Cells(row, 1).Value < 5 Then
            ws.Rows(row).Hidden = True
        End If
    Next row
End Sub

In this case, any row with a value less than 5 in column A will be hidden.

1. Hiding or Unhiding based on user input: You can prompt the user to input which rows or columns to hide or unhide. Here’s an example:

Sub HideRowsByUserInput()
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim userInput As String  
    Set ws = ThisWorkbook.ActiveSheet   
    ' Ask user for row number to hide
    userInput = InputBox("Enter the row number to hide:")
    rowNum = CLng(userInput)   
    ' Hide the row specified by the user
    ws.Rows(rowNum).Hidden = True
    MsgBox "Row " & rowNum & " has been hidden."
End Sub

Conclusion:

This example gives you a detailed overview of how to hide and unhide rows and columns in Excel using VBA. You can customize this code for various situations, such as hiding/unhiding rows or columns dynamically based on specific conditions or user input. The key concepts are manipulating the Rows and Columns properties and using the Hidden property to hide or unhide them.