Macro to hide rows based on the cell value

[ubns]

Hi Chandoo,


I am enrolled in your excel course. I was wondering if you or one of our fellow forum participants can assist in the following:


Hi,


I have input cell - Retirement Age.


I was wondering if it is possible for me to hide the rows based on the input in this cell. For example:


if the value in retirement age cell is 55 then it should hide all filled rows after that row.


At the same time I have graph, it should update the graph (I mean chart) to upto age 55 only.

 

[SirJB7]

Hi, ubns!

Perhaps you'd want to read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Check your other post for further info.

Regards!

 

[sgmpatnaik]

Hello Mr. ubns AS per SirJb7 it is better to send a sample file and any how here is command for you it might work what you required


Option Explicit


Private Sub Worksheet_Activate()


Dim HiddenRow&, RowRange As Range, RowRangeValue&


'*****************************

'< Set the 1st & last rows to be hidden >

Const FirstRow As Long = 4

Const LastRow As Long = 20


'< Set your columns that contain data >

Const FirstCol As String = "B"

Const LastCol As String = "G"

'*****************************


ActiveWindow.DisplayZeros = False

Application.ScreenUpdating = False


For HiddenRow = FirstRow To LastRow


'(we're using columns B to G here)

Set RowRange = Range(FirstCol & HiddenRow & _

":" & LastCol & HiddenRow)


'sums the entries in cells in the RowRange

RowRangeValue = Application.Sum(RowRange.Value)


If RowRangeValue <> 0 Then

'there's something in this row - don't hide

Rows(HiddenRow).EntireRow.Hidden = False

Else

'there's nothing in this row yet - hide it

Rows(HiddenRow).EntireRow.Hidden = True

End If


Next HiddenRow


Application.ScreenUpdating = True


End Sub