Hiding rows where text not entered to right of colums A and B

[Aidan]

I have a worksheet with narrative from A11:B60. Can someone provide me with a
macro that scans down rows 11 to 60 and if there is no content to the right
of columns A and B it hides these rows. i would still need to see the
headings for all the columns which stretch across to Column AD

Thanks in advance,


Aidan.

 

[OssieMac]

Hi Aidan,

Try the following. Note the comment and also that a space and underscore at
the end of a line is a line break in an otherwise single line of code.

I am a bit confused by "i would still need to see the headings for all the
columns which stretch across to Column AD". I should think the column
headers are in row 1 and will not be affected but if I have got it wrong then
please get back to me.

Sub HideBlankRows()
Dim rngToTest As Range
Dim cel As Range

'Edit "Sheet1" to your worksheet name
With Sheets("Sheet1")
Set rngToTest = Range(.Cells(11, "C"), _
.Cells(60, "C"))

For Each cel In rngToTest
If WorksheetFunction.CountA _
(Range(cel, .Cells(cel.Row, _
.Columns.Count))) = 0 Then

cel.EntireRow.Hidden = True

End If
Next cel

End With
End Sub