Geek Answers Handbook

Macro to hide rows in excel 2010

I am a little new to programming in VBA. I read something on the Internet, but I could not find what I needed, or could not get it to work. my problem:

on sheet 1 of the sheet in cell B6, the value for how many years the project will be used is indicated.

on sheets of sheets 2 and sheet 3, I made a table for 50 years (year from 1 to 50, line 7 - line 56).

in cell b6 in "sheet 1" I want to enter a value between 1 and 50. When the value is 49, I want to hide line 56 in "sheet2" and "sheet 3". when the value is 48, I want to hide 55:56 lines in "sheet2" and "sheet 3" etc. this is what i got so far, but i can't get it to work automatically when i change the value in cell B6:

Sub test1()
    If Range("sheet1!B6") = 50 Then
    Rows("52:55").EntireRow.Hidden = False
    Else
    If Range("sheet1!B6") = 49 Then
    Rows("55").EntireRow.Hidden = True
    Else
    If Range("sheet1!B6") = 48 Then
    Rows("54:55").EntireRow.Hidden = True

    End If: End If: End If:

    End Sub

, - .

+5
2

, , !

VBA, .

1- , . Excel "" , , .

2- lionz, Excel. .

3. ... , VBA. .

...

, Excel Sheet, lionz. 2, 2, 3 , .

, !

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim oSheet As Excel.Worksheet

    'We only want to do something if the changed cell is B6, right?
    If Target.Address = "$B$6" Then

        'Checks if it a number...
        If IsNumeric(Target.Value) Then

            'Let avoid values out of your bonds, correct?
            If Target.Value > 0 And Target.Value < 51 Then

                'Let assign the worksheet we'll show / hide rows to one variable and then
                '   use only the reference to the variable itself instead of the sheet name.
                '   It safer.

                'You can alternatively replace 'sheet 2' by 2 (without quotes) which will represent
                '   the sheet index within the workbook
                Set oSheet = ActiveWorkbook.Sheets("Sheet 2")

                'We'll unhide before hide, to ensure we hide the correct ones
                oSheet.Range("A7:A56").EntireRow.Hidden = False

                oSheet.Range("A" & Target.Value + 7 & ":A56").EntireRow.Hidden = True

            End If

        End If

    End If

End Sub
+5

. . . , .

Rows("52:55").EntireRow.Hidden = False


activesheet.Rows("52:55").EntireRow.Hidden = False

. . workheet_change VBA ( , 1 ). ( 2 ). , . . :

Private Sub Worksheet_Change(ByVal Target As Range)
test1
end Sub

. , - , test1.

+4

More articles:


All Articles