one formula that covers multiple cells in microsoft excel

Question

I always wondering if there's a formula that covers multiple cells,

example:

I am using A1 to A10 up to D1 to D10 cells..

I used a helper column at X1 and Y1

@ A1 I put formula which states that =if(X1<>Y1, "", X1)

then if A1 is "blank", I really wanted to AUTOMATICALLY HIDE  A2-A6 up to D2-D6,

so that A7-A10, up to D6-D10 will go upward in the position of A2- D2..

is there a way to do that..? please help me..

Thank you Very Much and God Bless..

RONEL

Answer 1

As Rajesh says, you need VBA to hide rows automatically.

As I understand, you want to hide rows 2 to 6 when cell A1 is empty and show rows 2 to 6 when A1 has a value. You could do this with the following code. Copy the code, then right click the sheet tab, select "View Code" and paste the code into the code window. 

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("1:1")) Is Nothing Then

    If Len(Range("A1")) = 0 Then

        Rows("2:6").EntireRow.Hidden = True

    Else

        Rows("2:6").EntireRow.Hidden = False

    End If

End If

End Sub

This assumes that the values in cells X1 and Y1 are entered manually.

Answer 2

Ronel,

There is no formula in Excel which can Hide a row or a column.

Hiding / Un-Hiding can only be done manually or else by use of an excel vba code ( macro ).

Answer 3

>> If Not Intersect(Target, Range("1:1")) Is Nothing

This line means:

Keep a watch on row 1 and if a change happens in row 1, then run the rest of the macro (which hides rows 2 to 6). If you change that to Range("1:22") then it will watch row 1 to row 22 and run the macro whenever any cell in these rows are changed. Since you said you wanted to hide/show the rows based on a values entered in X1, the macro was written to watch row 1.  If you want to watch cell X22 instead, you can use Range("X22") instead.

In Excel you can only hide whole rows, not parts of a row. You can't hide just rows 2 to 6 in only columns B to D. That is not possible.

Answer 4

Thank you Sir Rajesh C..

thanks for the information, atleast it is easy on my part now not to look  for any formula regarding auto hide..

thank you for responding,

Godbless

Answer 5

maam teylyn,

thank you..

i will try this asap..

hope it works..

Godbless