Macro to hide entire row if cell in column d of that row is empty

[djclifton]

I am trying to write a simple macro that will hide an entire row in in a worksheet if the cell in column D of that row is empty. Then unhide the entire row if the cell is then populated (by another macro .value).

I am absolutely useless at writing macros and any help would be awesome.

 

[Michael M]

THis will hide all the rows that are blank in "D"


I'd suggest you just incorporate this code into your other macro by putting this at the start of the other macro

Cells.EntireRow.Hidden = False'   will unhide the rows

with this further down in the code

Range("D1", Range("D" & Rows.Count).End(xlUp)).SpecialCells(4).EntireRow.Hidden = True 'will hide the blanks in "D"

 

[Trebor76]

Here's how you might put Michael's nifty suggestions into a macro:

Option Explicit
Sub Macro1()
    
    Dim lngLastRow As Long
    
    lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    With Range("D1:D" & lngLastRow)
        .EntireRow.Hidden = False 'Ensure all rows are unhidden
        .SpecialCells(4).EntireRow.Hidden = True 'will hide the blanks in "D1:D[lngLastRow]"
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Peter_SSs]

Two problems I see with both codes as written - need tweaking.

1. If there are no empty cells the codes will error.

2. Both codes could miss hiding (or showing) rows

i) If the data looked like this, both codes will hide row 3 but not row 5

Excel Workbook
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	x	x	x	x	x
3	x	x	x		x
4	x	x	x	x	x
5	x	x	x		x
Hide Unhide

ii) If the data looked like this (note rows 3 and 5 hidden because blank) and the OP's other code now puts a value in cell D5, Robert's code will not unhide that row.

Excel Workbook
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	x	x	x	x	x
4	x	x	x	x	x
6
Hide Unhide

My suggestion assumes a heading row, and incorporating the following into the other macro (or triggered by Worksheet_Change)

With ActiveSheet
  If .FilterMode Then .ShowAllData
  Intersect(.UsedRange, .Columns("D")).AutoFilter Field:=1, Criteria1:="<>"
End With