macro for Hide rows in excel 2010

Question

I m kinda new to programming in VBA  I read some stuff on the internet but I couldnt find what I need or couldnt get it working  My problem  in worksheet  sheet 1  in cell B6 a value is given for how many years a project will be exploited  in worksheets  sheet 2  and  sheet 3  i made a spreadsheet for 50 years   year 1 to year 50  row 7 to row 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 row 56 in  sheet2  and  sheet 3   when the value is 48 i want to hide rows 55 56 in  sheet2  and  sheet 3   and so on  this is what i got so far but i cant get it to work automaticly when i change the value in cell B6  Sub test1       If Range  quot sheet1 B6 quot     50 Then     Rows  quot 52 55 quot   EntireRow Hidden   False     Else     If Range  quot sheet1 B6 quot     49 Then     Rows  quot 55 quot   EntireRow Hidden   True     Else     If Range  quot sheet1 B6 quot     48 Then     Rows  quot 54 55 quot   EntireRow Hidden   True          End If  End If  End If           End Sub  i hope someone can help me with my problem  Thank you

User · Answer

You almost got it  You are hiding the rows within the active sheet  which is okay  But a better way would be add where it is   Rows  52 55   EntireRow Hidden   False   becomes   activesheet Rows  52 55   EntireRow Hidden   False   i ve had weird things happen without it  As for making it automatic  You need to use the worksheet change event within the sheet s macro in the VBA editor  not modules  double click the sheet1 to the far left of the editor   Within that sheet  use the drop down menu just above the editor itself  there should be 2 listboxes   The listbox to the left will have the events you are looking for  After that just throw in the macro  It should look like the below code    Private Sub Worksheet Change ByVal Target As Range  test1 end Sub   That s it  Anytime you change something  it will run the macro test1

User · Answer

Well  you re on the right path  Benno   There are some tips regarding VBA programming that might help you out    Use always explicit references to the sheet you want to interact with  Otherwise  Excel may  assume  your code applies to the active sheet and eventually you ll see it screws your spreadsheet up  As lionz mentioned  get in touch with the native methods Excel offers  You might use them on most of your tricks  Explicitly declare your variables    they ll show the list of methods each object offers in VBA  It might save your time digging on the internet     Now  let s have a draft code     Remember this code must be within the Excel Sheet object  as explained by lionz  It only applies to Sheet 2  is up to you to adapt it to both Sheet 2 and Sheet 3 in the way you prefer   Hope it helps   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 s a number            If IsNumeric Target Value  Then               Let s avoid values out of your bonds  correct              If Target Value  gt  0 And Target Value  lt  51 Then                   Let s 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 s 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   amp  Target Value   7  amp    A56   EntireRow Hidden   True              End If          End If      End If  End Sub

[excel] macro for Hide rows in excel 2010

Examples related to excel

Examples related to vba

Examples related to row

Examples related to hide