Determining last active row [duplicate]

Question

This question already has an answer here:

• Hiding cells by using VBA 1 answer
• Speed up Hiding/Unhiding rows based on criteria 3 answers
• How can I get my Macro to run on cell selection? 1 answer
• VBA to show/hide rows based on whether a cell's value is zero 2 answers
• hide/unhide rows - procedure too long 3 answers

hey everyone I'd like to make a macro that hides every row where in the Column E there's a 0 so that i'd have only rows with data in it.

Sub Hide_Columns_Containing_Value()
'Description: This macro will loop through a row and
'hide the column if the cell in row 1 of the column
'has the value of X.
'Author: Jon Acampora, Excel Campus
'Source:

Dim c As Range

    For Each c In Range("E5:E15").Cells
        If c.Value = "0" Then
            c.EntireRow.Hidden = True

            'You can change the property above to False
            'to unhide the columns.
        End If
    Next c

End Sub

This is what I've found yet but i'd like it to go through the whole column until blank and not only a range as the size of the range will change everytime Thank you for your time and answers !

Answer 1

You could use Union to gather the qualifying rows and hide in one go.

Option Explicit
Sub Hide_Columns_Containing_Value()
    Dim c As Range, unionRng As Range

    For Each c In Range("E5:E15").Cells
        If c.Value = "0" Then
            If Not unionRng Is Nothing Then
                Set unionRng = Union(unionRng, c)
            Else
                Set unionRng = c
            End If
        End If
    Next c
    If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True
End Sub

Answer 2

Your question is not "How to hide rows that have 0", your code for that works already.
Your question title should be How to find ActiveRange

Asking the proper questions helps you find better solutions, quicker.

Dim ws as WorkSheet: Set ws = Sheets("Sheet1")
Dim lr as Long
lr = ws.Cells(Rows.Count, "E").End(xlUp).Row

For each cell in ws.Range(ws.Cells(5, "E"), ws.Cells(lr, "E"))
   If cell = 0 Then
      cell.EntireRow.Hidden = True
   End If
Next cell

replace Sheets("Sheet1") with whatever your SheetName is.