VBA Hide rows if columns are "" and BLANK.

[Lewzerrrr]

Hi,

I have an analysis sheet with lots of tables of data, each table differs in length based upon a cell reference but I have formulas dragged down to the max to compensate if the data exceeds the previous one e.g IF(B2="","",INDEX)

I need a code that will hide all rows where the values in column E = "" or BLANK but to keep a space inbetween each table.

I need it to unhide if the tables get larger etc.

I also need to keep static rows hidden with this VBA (R103, R190, R232).

See below for examples (first table is before hidden, second is after being hidden, third is when it the data changes, fourth is after third being hidden)

	A	B	C	D			A	B	C	D
1	Header					1	Header
2	Data					2	Data
3	Data					3	Data
4	Data					4	Data
5						8
6						9	Header
7						10	Data
8						11	Data
9	Header					15
10	Data					16	Header
11	Data					17	Data
12						18	Data
13						19	Data
14						20	Data
15
16	Header
17	Data
18	Data
19	Data
20	Data

<colgroup><col width="56" span="11" style="width:42pt"> </colgroup><tbody>
</tbody>

	A	B	C	D			A	B	C	D
1	Header					1	Header
2	Data					2	Data
3	Data					3	Data
4	Data					4	Data
5	Data					5	Data
6	Data					6	Data
7	Data					7	Data
8						8
9	Header					9	Header
10	Data					10	Data
11						15
12						16	Header
13						17	Data
14
15
16	Header
17	Data
18
19
20

<colgroup><col width="56" span="11" style="width:42pt"> </colgroup><tbody>
</tbody>

 

[Coding4Fun]

This might work - Add this to the sheet by right clicking and view code, Once the data in the sheet is changed it will check all the cells and hide them accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)


Rows.Hidden = False


LastRow = Cells(Rows.Count, "A").End(xlUp).Row


For i = 2 To LastRow


If Cells(i, 5).Value = "BLANK" Or Cells(i, 5).Value = "" Or IsEmpty(Cells(i, 5).Value) Then
    
    If Cells(i, 1) = "" And Cells(i - 1, 1) = "" Then
    
        Rows(i).Hidden = True


    End If


End If
Next i


Rows(103).Hidden = True
Rows(190).Hidden = True
Rows(232).Hidden = True


End Sub