Determining last active row

Question

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 !

If you google your question title you will find countless results. — SJR, Jul 09 '18 at 15:36
`"0" <> 0` i.e. text-that-looks-like-a-number is not the same thing as a real number. — , Jul 09 '18 at 16:10
@ScottCraner, Jeeped I disagree this is necessarily a duplicate of the hiding rows based on critieria. Or well, to be more precise, that is indeed the endgoal of the OP here, but he actually answered his own question's title, in the code he copy-pasted in his question's body. If you however read the text, what OP is actually trying to do is to find the actively used range / how to determine the last active row. Not that it changes much, as that would be a duplicate either way, but maybe just for the clarity sake the title should be edited and the duplicate reference adjusted accordingly. — Samuel Hulla, Jul 09 '18 at 16:20

score 3 · Answer 1 · answered Jul 09 '18 at 15:38

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

Samuel Hulla · Accepted Answer · 2018-07-09T16:17:25.480

1

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.

edited Jul 09 '18 at 16:17

answered Jul 09 '18 at 16:13

Samuel Hulla

6,617
7
36
70

Quick update! Sorry but I already +1'ed you. – Jul 09 '18 at 16:18
what would i put instead of the cell = 0 if i wanted it to compare to another column like if E6 < A6 you hide the row? – Basho Jul 10 '18 at 14:14
@Basho Better asked as a seperate question (and what I mean under that, is to also do your reasearch with it, because that has already been answered numerous times). Not that I don't want to help you, but it's pretty difficult to explain it in the comments like so and I don't want to be editing an answer which would confuse somebody who actually stumbled upon this question via internet search and looked for the specific solution – Samuel Hulla Jul 10 '18 at 14:17

Determining last active row

2 Answers2

Linked