Hide row if cell is blank

[Littlemalky]

I have a range("D35:D44"), and if any of those cells do not contain a value in them, then I need the entire row to be hidden, so that only the cells in that range are shown. All the code I've found is for a specific value it seems, rather than a blank cell.

 

[VoG]

Try this

Sub Hiderows()
Dim c As Range
For Each c In Range("D35:D44")
    c.EntireRow.Hidden = c.Value = ""
Next c
End Sub

 

[MrKowz]

Try:

Public Sub HideBlanks()
Range("D35:D44").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

 

[Littlemalky]

Thanks guys! That worked beautifully.

 

[Littlemalky]

If i wanted to implement this same technique, but delete the rows in range from D2:H, how would that work? I tried to change hidden to delete and use a LastRow reference, but it is telling object not found.

 

[VoG]

Try

Sub DelRows()
Dim LR As Long
LR = Range("D" & Rows.Count).End(xlUp)
Range("D2:D" & LR).SpecialCells(xlCellTypeBlanks).Resize(, 5).Delete shift:=xlShiftUp
End Sub

 

[Littlemalky]

Hmm, doesn't seem to be doing anything. I have part numbers in column A, a description in column B, and quantities in column C, and i've done a vlookup in column D to return a value.

How about if a value exists, then delete row. If it's blank leave it alone.

 

[VoG]

Try

Sub DelRows()
Dim LR As Long, i As Long
LR = Range("D" & Rows.Count).End(xlUp)
For i = LR To 2 Step -1
    If Range("D" & i).Value = "" Then Range("D" & i).Resize(, 5).Delete shift:=xlShiftUp
Next i
End Sub

 

[Littlemalky]

Says type mismatch

 

[Littlemalky]

I changed it to this and it worked perfectly for what i need.

Private Sub DelRows2()
    Dim LR As Long, i As Long
    LR = Cells(Rows.Count, "D").End(xlUp).Row
    For i = LR To 2 Step -1
        If Range("D" & i).Value > 0 Then Range("D" & i).Resize(, 5).Delete shift:=xlShiftUp
    Next i
End Sub

Thanks for all your help!