Did you hear about Malcolm Gladwell?
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.
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.
At first, try to solve it by yourself. If you cannot, don’t check the solution immediately.
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 🙂
Here are the problem criteria:
- He might have 5-20 or maybe more worksheets in his workbook,
- All the worksheets will hold identical data,
- The first row of every worksheet will be used for some arbitrary information,
- The second row of every worksheet will have these two headings: Number and Date in cells A2 and B2,
- 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.
- 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.
- Hidden rows will be filled with a light red color,
- 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.
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:
- When someone will delete data from any cell of the range B3: B20000, the relevant rows from other worksheets will be unhidden automatically.
- 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.
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
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.
Download Working File
Download the working file from the link below: