Hide Blank Rows in a column

[grogmi1]

Hi,

I am looking for VB code that will hide any blank rows within my list. I hve a list of numbers in column c and a seperate formula removes the staff number from the list when they have been give a job. The list of staff is seperate on sheet 4 and their numbers are in column c. I have been tinkering about with the code below which can hide individual rows but I need something that will search down column C and hide any rows which are blank. Any help would be much appreciated

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("c:c")
If .Value = "" Then
.EntireRow.Hidden = True
Else
.EntireRow.Hidden = False
End If
End With
stoppit:
Application.EnableEvents = True
End Sub

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] HideC()
    Columns("C:C").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = [COLOR="Blue"]True[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[grogmi1]

Thanks for the quick reply, sorry I should have been a bit more specific. The list of staff numbers starts at row 5 in column C as i have row headers and the panes frozen, I have tried the code you gave me and it only hides the blank rows 1 to 4 in column C. I should also let you know I'm no VB expert !!

Thanks again

 

[grogmi1]

Sorry also, just to add, the cells in column C contain formulas, not sure if that make any difference

 

[Sektor]

You mean that there are formulas which produce empty string?

 

[grogmi1]

Yeah, sorry!!

The formulas in column C look to a different sheet to see if a staff number has been used, if it has then it "blanks" the cell out

Thanks

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] HideC()
    
    [COLOR="Blue"]Dim[/COLOR] lastRow [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], rng [COLOR="Blue"]As[/COLOR] Range
    
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
[COLOR="seagreen"]    ' Insert dummy row.[/COLOR]
    Rows(1).Insert
    
    [COLOR="Blue"]With[/COLOR] Range("C1")
        .Value = "ABC"
        .AutoFilter Field:=1, Criteria1:="="
        [COLOR="Blue"]Set[/COLOR] rng = Range("C2:C" & lastRow + 1).Cells.SpecialCells(xlCellTypeVisible)
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] rng [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
            .AutoFilter
            rng.EntireRow.Hidden = [COLOR="Blue"]True[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
[COLOR="seagreen"]    ' Turn off autofilter, if any.[/COLOR]
    [COLOR="Blue"]With[/COLOR] ActiveSheet
        [COLOR="Blue"]If[/COLOR] .AutoFilterMode [COLOR="Blue"]Then[/COLOR] .AutoFilter.Range.AutoFilter
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
[COLOR="SeaGreen"]    ' Delete dummy row.[/COLOR]
    Rows(1).Delete
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[grogmi1]

Hi Sektor,

Thanks again, when I use the code from you last post it hides everything (blank cells and cells with staff numbers in) up to row 182?
Any ideas?

Thanks