Find function, need line number

[uniden32]

I currently have a macro, that goes through our employee list, if their tip value = 0, it hides that line. I have it hard coded where it will do this for 138 lines (current number of employees).

I find that as our employee number changes, I have to constantly go in, and edit this macro code, and was wondering if there is a way to search for the last employee name (Zycher) and use that line number in the code.

So basically, I'd have to seach for Zycher, figure out what line number his name appears, that assign that line number to an integer or whatever and use that in the For count line if possible (For count = 5 to $Zycher)

This is the code I currently have:

Sub hide()
Dim count As Integer
For count = 5 To 138
If Cells(count, 11).Value < 1 Then
Rows(count).Hidden = True
End If
Next
End Sub

Thanks in advance as always.

 

[Trebor76]

Hi there,

Try this where it finds the last row in column K regardless of what the entry is, and works upwards (this is best for hidding or deleting rows) to row 5:

Option Explicit
Sub hide()
    
    'http://www.mrexcel.com/forum/showthread.php?t=581412

    Dim lngLastRow As Long, _
        lngActiveRow As Long
        
    lngLastRow = Cells(Rows.count, "K").End(xlUp).Row
    
    For lngActiveRow = lngLastRow To 5 Step -1
        If Cells(lngActiveRow, "K").Value < 1 Then
            Rows(lngActiveRow).Hidden = True
        End If
    Next lngActiveRow
    
End Sub

Note you may need to add to the If statement to unhide the row if it's greater than 1 as it may already be hidden from a previous run of the macro.

HTH

Robert