Automatically Hide Emptry Rows in Excel Using a Macro Coded Button (VBA)

Dealing with a very large amount of data in your Excel files will be very tedious especially when there are some issues with the data. I have been across multiple data tables that I needed to work on from computing large amounts of budget to multiple data for consolidation from performances of learners.

Ultimately, there are so many possible scenarios that would make you go berserk when your dealing with a large amount of data. Among the many things I've encountered when I was in my early years with MS Excel is to deal with data that are unavailable on certain rows and columns.

I remembered that it was around 2013 or 2014 when I had to deal with such issue. I was given a reporting template that was designed to segregate gender of each class. However, the template itself was designed for 500 learners, 250 each for each gender.

And obviously, there are instances where the total number of learners will not reach the maximum total of the template which results to blank rows. So, I have blank rows or unused rows in my upper table (male) and lower table (female). 

Another instance was when I was given a consolidation template from multiple schools, wherein each school had their own total of male and female. This resulted to segregated tables between each school and for each gender. And guess what? It resulted to multiple blank rows between each school, between each gender on the same sheet!

This took so much time for me to hide all the empty rows since I needed to check each school and each category slowly just to see where there are data in it, and where there's none.

Thus, I tried to learn how to use VBA Codes in dealing with my Excel reports. And this brought me to my HIDE ROWS BUTTON. 

I was so in love with the function that most, if not all, of MS Excel designs have this function. 

PROS:

1. I don't need to worry about multiple blank rows that are separated or are between rows that have data in it.
2. I can populate my Excel sheet for others to work on without worrying if they skip rows.
3. I can design a large MS Excel Sheet that could cater on multiple categories for every user even if they won't fill the whole table.

If you want to watch how this MS Excel Feature works, you can check the video down below. It'll explain in detail how to use the VBA code below step-by-step.

VBA Code used in this tutorial:

Sub HideRow()

Dim isfLastRow As Long

Dim isfCounter As Long

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    Application.EnableEvents = False

    

    With ActiveSheet

    

    .Shapes("HideButton").Visible = True

    

    isfLastRow = .Range("A10000").End(xlUp).Row

    For isfCounter = 5 To isfLastRow

        If .Cells(isfCounter, "A").Value = 0 Then

            .Cells(isfCounter, "A").EntireRow.Hidden = True

        End If

    Next isfCounter

    .Range("A1").Select

    

    End With

    

    Application.EnableEvents = True

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

    

End Sub

 

In case you still have questions or issues with applying this VBA Codes, you can just post it down below and we'll attend to it as soon as we can!