[Excel VBA Challenge] When you input data into a row in one worksheet, How to hide the same rows in other worksheets?

Did you hear about Malcolm Gladwell?

Didn’t you?

He is the inventor of a rule called the 10,000 Hour Rule. According to his rule, you need 10,000 hours of practice to achieve actionable expertise on a topic. If you work 8 hours per day on a topic and your week is of 5 days then you will need around 5 years to be an expert on that topic. If you work more, you can achieve that milestone within 3 years. So it is not that easy to be an expert on a topic. You will need a strong and dedicated practice schedule.

So…

Do you want to be an expert on Excel VBA? What is the rule? Yea that 10,000 Hour Rule. Follow that rule and solve as many problems as possible.

Today I have an Excel VBA challenge for you. The solution is also in this post.

But…

At first, try to solve it by yourself. If you cannot, don’t check the solution immediately.

Think…think…think…

Think about the possible solutions for one day or two days. When your head is not giving you any solution, you feel your head is stuck with the problem, it’s time to get the solution. Come to this post again and get the solution.

The problem I am going to throw for you is not my problem. I got this problem from one of my USA friends. He is a boxer 🙂

Related: Learn Excel VBA Programming & Macros [Free Tutorials, Download PDF & Course]

The problem

Here are the problem criteria:

  1. He might have 5-20 or maybe more worksheets in his workbook,
  2. All the worksheets will hold identical data,
  3. The first row of every worksheet will be used for some arbitrary information,
  4. The second row of every worksheet will have these two headings: Number and Date in cells A2 and B2,
  5. From cell A3 and B3, the user will input data; under the Number column, the user will input numbers like 1, 2, 3, 4, 5, … and under the Date column, the user will input dates.
  6. The condition is: when the user will input date values under the Date column, the relevant rows will be hidden in other worksheets. Let me explain it with an example. Say the workbook has 5 worksheets. The worksheets are Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5. The user is in the Sheet3 worksheet and has input some date into cell B7 (in the 7th row). So the 7th row of Sheet1, Sheet2, Sheet4, and Sheet5 worksheets will be hidden.
  7. Hidden rows will be filled with a light red color,
  8. And the macro will run when the user will press Enter key after entering the date values under the Date column.

…Is that clear?

Here are two images to help you understand the problem more clearly.

Hide Row

A data is entered into cell B6. So 6th row will be hidden in other worksheets.

Hide Row

This is the image of another worksheet in the same workbook and you see the 6th row is hidden.

Start Brainstorming and solve the problem.

Here goes the solution to the above problem.

Option Explicit

Sub HidingRow()

    Dim i As Integer
    Dim j As Integer
    Dim CurrentIndex As Integer
    Dim TotalWorksheets As Integer

    CurrentIndex = ActiveSheet.Index
    TotalWorksheets = Worksheets.Count

    For i = 3 To 20000
        If IsEmpty(ActiveSheet.Cells(i, 2).Value) = False Then
                For j = 1 To TotalWorksheets
                    If j <> CurrentIndex Then
                        Worksheets(j).Rows(i & ":" & i).Interior.Color = RGB(235, 137, 137)
                        Worksheets(j).Rows(i & ":" & i).Hidden = True
                    End If
                Next j
        End If
    Next i
End Sub

And as the workbook open event I have set this code:

Private Sub Workbook_Open()
    Run "HidingRow"
    Application.OnKey "~", "HidingRow"
End Sub

Additional Features that you can add to

These are the additional features that you can add to this code:

  1. When someone will delete data from any cell of the range B3: B20000, the relevant rows from other worksheets will be unhidden automatically.
  2. Make an automatic system as though the user can change the range from B3: B20000 to any like B3: B25000 or B3: B50000 and so on.

Read More: How to Use the Select Case Structure in Excel VBA

Solution of the Problem by Andy Pope

Andy pope provided a great solution to the above problem. Andy Pope runs this great blog and he is a Microsoft MVP. The code he provided is more efficient than me with fewer iterations. A real great Coder 🙂

You can check out the code below. You have to run the code within the ThisWorkbook code module.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim OtherSheet As Worksheet
    Dim HideRow As Boolean

    If Target.Cells.Count = 1 Then
        If Not Intersect(Sh.Columns(2), Target) Is Nothing Then
            If Target.Row >= 3 Then
                HideRow = Len(Target.Value) > 0
                For Each OtherSheet In ThisWorkbook.Worksheets
                    If StrComp(Sh.Name, OtherSheet.Name, vbTextCompare) = 0 Then
                        ' ignore updating sheet
                    Else
                        OtherSheet.Rows(Target.Row).Hidden = HideRow
                    End If
                Next
            End If
        End If
    End If

End Sub

Wrapping Up

If you believe in acquiring Transformative Knowledge on a topic like mine, then you must solve problems regularly on that topic. That’s the secret to becoming an expert on some subject. Excel VBA is not tough to be an expert on, but without solving problems, there is no way.

*On any query about the above program, ask me using the comments box.

Read More…

How to Highlight Every Other Row in Excel

Viewing Excel Worksheets in Multiple Ways

Two types of VBA Macros: VBA Sub procedures & VBA functions

Download Working File

Download the working file from the link below:

Sebastian-Ronnfeldt-Problem.xlsm

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment
  1. I made a template about row hide-show property.
    You can hide and show columns with userform .Columns are listed in the listbox.
    For Download Template : http://adf.ly/1IWYRn

Leave a reply

ExcelDemy
Logo