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

  • Thread starter Thread starter Aidan
  • Start date Start date
A

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.
 
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
 
Back
Top