Prev: Modeless dialog box appears twice on screen???
Next: Problem copying table data as a picture onto chartsheet
From: Aidan on 23 Dec 2009 07:18
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.
From: OssieMac on 23 Dec 2009 16:59
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

--
Regards,

OssieMac


"Aidan" wrote:

> 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.
 | 
Pages: 1
Prev: Modeless dialog box appears twice on screen???
Next: Problem copying table data as a picture onto chartsheet