Hide Rows Which Do Not Contain Data

[marka87uk]

Hi,

I have a large spreadsheet which I wish to make easier to use.

With a macro, would it be possible to hide each row where there is no cells with data entered in columns F to R?

Thanks in advance.

 

[VoG]

Try

Sub HideBl()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("F" & Rows.Count).ernd(xlUp).Row
For i = LR To 1 Step -1
    Rows(i).Hidden = WorksheetFunction.CountA(Range("F" & i).Resize(, 13)) = 0
Next i
Application.ScreenUpdating = True
End Sub

 

[marka87uk]

Thanks but it did not seem to work unfortunately (I did correct your typo 'End' before running).

 

[VoG]

Sorry about the typo. It works for me. To clarify, should it hide rows where all of F:R are blank? Are the cells actually blank or could they contain spaces?

 

[marka87uk]

No problem - I've just checked and some cells do contain spaces! (It must be the way the data is exported from an application.)

Would it be possible to adjust the code to also hide the rows if the cells only contain spaces?

 

[marka87uk]

Got it!

I added in the following, unless you know of a better way?

Rows(i).Hidden = WorksheetFunction.CountA(Range("F" & i).Resize(, 13)) = 0
Rows(i).Hidden = Range("F" & i) = " "

 

[marka87uk]

Oops sorry that doesn't work as it hides all rows where column F contains a space regardless of whether there is text in other columns...

 

[VoG]

Maybe this

Sub HideBl()
Dim LR As Long, i As Long, j As Long
Application.ScreenUpdating = False
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    For j = 6 To 18
        If Cells(i, j).Value = " " Then Cells(i, j).ClearContents
    Next j
    Rows(i).Hidden = WorksheetFunction.CountA(Range("F" & i).Resize(, 13)) = 0
Next i
Application.ScreenUpdating = True
End Sub

 

[marka87uk]

Thanks! It's working now.