Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Vba: How To Hide A Row (or Column) With Vba

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi

I would like to know, how could be possible to "hide" a row in the sheet with the VBa and later show it again. I can clear, delete,... it, but not hide.
I am making some kind of calculation price list and under some conditions, few rows has to be hidden.
Thank you for your help.

Bye-bye

View Answers     

Similar Excel Tutorials

Hide or Unhide a Worksheet by Hand in Excel
I'll show you how to hide worksheets in Excel so that a user cannot see them but you can still access data on them ...
Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...
Easy Way to Manage Names in Excel
How to manage Names in Excel so they are easy to view and change as needed. This is a little trick that I use to k ...
Remove Gridlines in Excel 2007 and Later
In Excel 2007 and later you can quickly remove the gridlines that appear within the Excel worksheet. This allows yo ...

Helpful Excel Macros

Hide Specific Comments in Excel - Comments Will Still Display on Hover
- Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
Hide Comments in Excel Completely - Even Indicators Will not Appear
- Hide all comments in an Excel workbook. No indicators will be displayed and comments will not appear when you hover ove
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Delete Hidden Worksheets
- This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop up for

Similar Topics







Is there a shorter code I can use to hide/unhide rows with a VBA button?

There are no "fixed" range number of rows between Header to Header; they are random. Example: There are less rows between Header 1 to Header 2 then there are rows between Header 2 to Header 3, etc.

Header 1 at (A2:K2)

range: ( A4 : K190 )

***hide every 6 rows*** (A4 :K9), (A11:K16),(A18:K23), etc.

***then hide 5 rows range*** (A186: K190 )

Header 2 at (A191:K191)

range: ( A193 : K386 )

***hide every 6 rows, range:*** ( A193 :K198), (A200:K205),(A207:K212), etc.

***then hide 5 rows range*** (A382: K386 )

Header 3 at (A387:K387)

range: ( A389 : K582 )

***hide every 6 rows*** ( A389 :K394), (A396:K401), (A403:K408), etc.

***then hide 5 rows range*** (A578: K582 )

Header 4 at (A583:K583)

range: ( A585 : K701 )

***hide every 6 rows*** ( A585 :K589), (A592:K597), (A599:K604), etc.

***then hide 5 rows range*** (A697: K701 )

Here is the code I used, but I had to type in every range for it to work correctly.

Code:

Sub Hidden()
Application.ScreenUpdating = False
Rows("4:9").Hidden = Not Rows("4:9").Hidden
Rows("11:16").Hidden = Not Rows("11:16").Hidden
Rows("18:23").Hidden = Not Rows("18:23").Hidden
Rows("25:30").Hidden = Not Rows("25:30").Hidden
Rows("32:37").Hidden = Not Rows("32:37").Hidden
Rows("39:44").Hidden = Not Rows("39:44").Hidden
Rows("46:51").Hidden = Not Rows("46:51").Hidden
Rows("53:58").Hidden = Not Rows("53:58").Hidden
Rows("60:65").Hidden = Not Rows("60:65").Hidden
Rows("67:72").Hidden = Not Rows("67:72").Hidden
Rows("74:79").Hidden = Not Rows("74:79").Hidden
Rows("81:86").Hidden = Not Rows("81:86").Hidden
Rows("88:93").Hidden = Not Rows("88:93").Hidden
Rows("95:100").Hidden = Not Rows("95:100").Hidden
Rows("102:107").Hidden = Not Rows("102:107").Hidden
Rows("109:114").Hidden = Not Rows("109:114").Hidden
Rows("116:121").Hidden = Not Rows("116:121").Hidden
Rows("123:128").Hidden = Not Rows("123:128").Hidden
Rows("130:135").Hidden = Not Rows("130:135").Hidden
Rows("137:142").Hidden = Not Rows("137:142").Hidden
Rows("144:149").Hidden = Not Rows("144:149").Hidden
Rows("151:156").Hidden = Not Rows("151:156").Hidden
Rows("158:163").Hidden = Not Rows("158:163").Hidden
Rows("165:170").Hidden = Not Rows("165:170").Hidden
Rows("172:177").Hidden = Not Rows("172:177").Hidden
Rows("179:184").Hidden = Not Rows("179:184").Hidden
Rows("186:190").Hidden = Not Rows("186:190").Hidden
Rows("193:198").Hidden = Not Rows("193:198").Hidden
Rows("200:205").Hidden = Not Rows("200:205").Hidden
Rows("207:212").Hidden = Not Rows("207:212").Hidden
Rows("214:219").Hidden = Not Rows("214:219").Hidden
Rows("221:226").Hidden = Not Rows("221:226").Hidden
Rows("228:233").Hidden = Not Rows("228:233").Hidden
Rows("235:240").Hidden = Not Rows("235:240").Hidden
Rows("242:247").Hidden = Not Rows("242:247").Hidden
Rows("249:254").Hidden = Not Rows("249:254").Hidden
Rows("256:261").Hidden = Not Rows("256:261").Hidden
Rows("263:268").Hidden = Not Rows("263:268").Hidden
Rows("270:275").Hidden = Not Rows("270:275").Hidden
Rows("277:282").Hidden = Not Rows("277:282").Hidden
Rows("284:289").Hidden = Not Rows("284:289").Hidden
Rows("291:296").Hidden = Not Rows("291:296").Hidden
Rows("298:303").Hidden = Not Rows("298:303").Hidden
Rows("305:310").Hidden = Not Rows("305:310").Hidden
Rows("312:317").Hidden = Not Rows("312:317").Hidden
Rows("319:324").Hidden = Not Rows("319:324").Hidden
Rows("326:331").Hidden = Not Rows("326:331").Hidden
Rows("333:338").Hidden = Not Rows("333:338").Hidden
Rows("340:345").Hidden = Not Rows("340:345").Hidden
Rows("347:352").Hidden = Not Rows("347:352").Hidden
Rows("354:359").Hidden = Not Rows("354:359").Hidden
Rows("361:366").Hidden = Not Rows("361:366").Hidden
Rows("368:373").Hidden = Not Rows("368:373").Hidden
Rows("375:380").Hidden = Not Rows("375:380").Hidden
Rows("382:386").Hidden = Not Rows("382:386").Hidden
Rows("389:394").Hidden = Not Rows("389:394").Hidden
Rows("396:401").Hidden = Not Rows("396:401").Hidden
Rows("403:408").Hidden = Not Rows("403:408").Hidden
Rows("410:415").Hidden = Not Rows("410:415").Hidden
Rows("417:422").Hidden = Not Rows("417:422").Hidden
Rows("424:429").Hidden = Not Rows("424:429").Hidden
Rows("431:436").Hidden = Not Rows("431:436").Hidden
Rows("438:443").Hidden = Not Rows("438:443").Hidden
Rows("445:450").Hidden = Not Rows("445:450").Hidden
Rows("452:457").Hidden = Not Rows("452:457").Hidden
Rows("459:464").Hidden = Not Rows("459:464").Hidden
Rows("466:471").Hidden = Not Rows("466:471").Hidden
Rows("473:478").Hidden = Not Rows("473:478").Hidden
Rows("480:485").Hidden = Not Rows("480:485").Hidden
Rows("487:492").Hidden = Not Rows("487:492").Hidden
Rows("494:499").Hidden = Not Rows("494:499").Hidden
Rows("501:506").Hidden = Not Rows("501:506").Hidden
Rows("508:513").Hidden = Not Rows("508:513").Hidden
Rows("515:520").Hidden = Not Rows("515:520").Hidden
Rows("522:527").Hidden = Not Rows("522:527").Hidden
Rows("529:534").Hidden = Not Rows("529:534").Hidden
Rows("536:541").Hidden = Not Rows("536:541").Hidden
Rows("543:548").Hidden = Not Rows("543:548").Hidden
Rows("550:555").Hidden = Not Rows("550:555").Hidden
Rows("557:562").Hidden = Not Rows("557:562").Hidden
Rows("564:569").Hidden = Not Rows("564:569").Hidden
Rows("571:576").Hidden = Not Rows("571:576").Hidden
Rows("578:582").Hidden = Not Rows("578:582").Hidden
Rows("585:590").Hidden = Not Rows("585:590").Hidden
Rows("592:597").Hidden = Not Rows("592:597").Hidden
Rows("599:604").Hidden = Not Rows("599:604").Hidden
Rows("606:611").Hidden = Not Rows("606:611").Hidden
Rows("613:618").Hidden = Not Rows("613:618").Hidden
Rows("620:625").Hidden = Not Rows("620:625").Hidden
Rows("627:632").Hidden = Not Rows("627:632").Hidden
Rows("634:639").Hidden = Not Rows("634:639").Hidden
Rows("641:646").Hidden = Not Rows("641:646").Hidden
Rows("648:653").Hidden = Not Rows("648:653").Hidden
Rows("655:660").Hidden = Not Rows("655:660").Hidden
Rows("662:667").Hidden = Not Rows("662:667").Hidden
Rows("669:674").Hidden = Not Rows("669:674").Hidden
Rows("676:681").Hidden = Not Rows("676:681").Hidden
Rows("683:688").Hidden = Not Rows("683:688").Hidden
Rows("690:695").Hidden = Not Rows("690:695").Hidden
Rows("697:701").Hidden = Not Rows("697:701").Hidden
Application.ScreenUpdating = True
End Sub


--------------------------------------------------
Excel 2007 and Windows XP Professional


Hi,
I have three rows and 10 columns in my excel sheet
I'm trying to hide rows if the below conditions are matched:
if values of cells A1 through A10 are equal to either "/bin/touch" or "/bin/rm" and values of cells B1 through B10 are equal to either "b" or "f" then hide the entire row; else nothing should be done

how do i put these conditions into a macro,
below is my effort towards a Formula:
Code:

=IF(AND(OR($A1:$A10=""/bin/touch"",$A1:$A10=""/bin/rm""),OR($B1:$B10=""f"",$B1:$B10=""b"")),Selection.EntireRow.Hidden = True,"")


dont know if empty string is the way to give it for the false condition

here is my macro:
Code:

Sub HR()
' Hide rows if conditions match
    Range("A1:C10").Select
End Sub


Please find attached is the excel sheet for further clarification.

thank you for your time.




Simple request for all the gurus he
I have created a sheet that is a template for data input with many rows that are unused. For every row that is unused, a cell in the column V will display "Hide"

Basically, I'm trying to create a button that hides and unhides the unused rows. Ideally one button that toggles between hide and unhide.
If a cell in column V displays "Hide" then hide that entire row.
Before rows are hidden, display "Hide unused rows" on the button and hide rows upon clicking
After rows are hidden, display "Unhide rows" with a command to unhide the unused rows

Any help is appreciated!


I'm a VBA newbie and I'm trying to find out how to hide a range of rows based on multiple conditions. I've looked through several threads and I haven't had much luck. Any help would be appreciated. Here's what I'm trying to do:

If G10 = "Summary Charts", then hide rows 6:9
If G10 = "Selection B", then hide row 9
If G10 = "Selection C", then hide rows 8:9

I have about 7 conditions similar to what is described above.

I want this to be dynamic, since it is based on the selection made within a drop down menu.

I have a Jet Report that I created which works great. After the report is created, it auto-hides any column or row that has a jet function in it to make the report easier to read. What i am trying to do is delete any column or row that has HIDE at the beginning. example below-

A B C D E F G
1 hide hide hide hide hide
2 hide
3
4 hide
5
6
7

After the report is saved, Columns ABCEF are hidden, but still there, i don't need this data any longer and would like to remove it from the document without affecting the original excel document.

any suggestions?


Dummy Worksheet Attached

I have a toggle button that scrolls through 1 to 4 levels of detail for the above sheet. I want to hide rows based on the toggle button value (linked to Cell A1) and the value in Column H.

Criteria a

A1 = 1 Hide all rows where H doesn't = 1 or ""
A1 = 2 Hide all rows where H doesn't = 2 0r ""
A1 = 3 Hide all rows where H doesn't = 3 0r ""
A1 = 4 Hide all rows where H doesn't = 4 0r ""

I also want to hide rows where the value in Column M = 0.

I did manage to write something that I thought would work, but it got hideously stuck in a loop. I want the code to run as quickly as possible, I know if I work it out that it won't.

Column H contains numbers until it gets to the first row that doesn't need to be hidden, then column H contains "". An efficient solution would work from this cell upwards I think.

Hope you can help!

Cheers




Hi all,

I have a kind of challenge (well, for me it's a challenge... )

What i want is a VBA script (if neccessary) that automaticly hides rows when a particular value is entered in a cell.

For example:
In sheet1, cell c1 is where the value has to be put in (001, 002, 003 etc.). In sheet 2 i made some sort of database.

Activity-Selection
001-1 (hide row 3, 4 and 5)
002-2 (hide row 5, 6 and 7)
003-3 (hide nothing)
004-1 (hide row 3, 4 and 5)

Selection is the kind of trigger that would autohide the rows.

So all in all.
If i enter "001" in cell c1 in sheet 1, it shoud hide automaticly row 3, 4 and 5.

Please help me Excel Guru's!

I need a macro to hide all the rows except one every 7 days in one button .
But i need quite a help in it.

so far I have this.

Private Sub Hide_Click()
If Hide.Caption = "Hide Columns" Then
Rows("A5:A10").SelectSelection.EntireColumn.Hidden = True
Hide.Caption = "Show rows"
Else
Rows("A5:A10").SelectSelection.EntireColumn.Hidden = False
Hide.Caption = "Hide Columns"
End If
End Sub




Hi,

I'm new in VBA programming.
I have a combo box with 6 options; linked to cel C2
When I choose the first option I would like to hide row 14, 15, 23, 24, 25, 26, 29, 30, 31, 32, 33, 34 and 35.
When I choose the second option I would like to hide row 14, 15, 24, 25, 26, 29, 30, 31, 32, 33, 34 and 35.
When I choose the third option I would like to hide row 15, 25, 26, 34 and 35.
When I choose the forth option I would like to hide row 25, 26, 34 and 35.
When I choose the fifth option I would like to hide row 26, 34 and 35.
When I choose the sixth option I would like to show all.

I did:

'Collateral type'
Private Sub ComboBox1_Change()

Select Case ComboBox1.Value

Dim Rng As Range

Case Is = "1"
Rows("14:15,23:26,29:36").EntireRow.Hidden = True
Case Is = "2"
Rows("14:15,24:26,29:35").EntireRow.Hidden = True
Case Is = "3"
Rows("15:15,25:26,34:35").EntireRow.Hidden = True
Case Is = "4"
Rows("25:26,34:35").EntireRow.Hidden = True
Case Is = "5"
Rows("26:26,34:35").EntireRow.Hidden = True
Case Is = "6"
Rows("11:11,35:35").EntireRow.Unhidden = True

End Select

End Sub

But it didn't work..

Thanks in advance.

I'm making a formulary where 2 people are involved.
It has a few radio buttons. But to make it more clear for the first person I want to hide the rows he doesn't need to fill. However, as I hide the rows, the radio buttons are not hidden and just move upwards.
Is there a way to "fix" the buttons to a cell so that I can hide/unhide them along with the row?
Thanks!!


First, thank you for any help you can give. I'm not the best at creating macros, so I need help with this one.

I have a drop down list in cell I3 with a range of numbers from 0-8. If 0 is selected I want to hide rows 14-69.

If 1 is selected hide rows 21-69
If 2 is selected hide rows 28-69
If 3 is selected hide rows 35-69
If 4 is selected hide rows 42-69
If 5 is selected hide rows 49-69
If 6 is selected hide rows 56-69
If 7 is selected hide rows 63-69
If 8 is selected hide rows No hidden Rows

I also need this macro to work to work it the worksheet is coppied into the same workboob multiple times. Is this possible? Thank you for your help!


I have a growing number of rows of data starting at row 6. The 6th column of any row contains the letter "p" or is blank. I want to hide all the rows with "p" in the 6th column. Then another macro to unhide all hidden rows from row 6 to row 1000.
The following works to hide a specific row:
VB:

If ActiveCell.Offset(0, 5) = "p" Then 
    Rows("6:6").EntireRow.Hidden = True 
    ActiveCell.Offset(1, 0).Range("A1").Select 
End If 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




I am trying to hide rows in a sheet based on a condition in one of the cells in that row. I am using Excel 2010.

I have attached the sheet and am trying to hide all rows which have either "CANCELLED" or "COMPLETE" in column B. This causes 2 problems. Firstly I have tried to use the following code, and just paste it into the editor in Excel VBA, but then, quick as a flash, nothing happens. It may be just me being a VB novice (haven't used it for many, many years), but I just can't get it to run.
VB:

Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Target.Value = "COMPLETE" Then 
        Target.EntireRow.Hidden = True 
    Else: Target.EntireRow.Hidden = False 
    End If 
     
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



The second problem I can foresee is that as most of the cells in the row are merged, it will only hide the upper cell in the merged range (Row 7 in this case) when I want it to hide all 6 rows which are merged, and the 6 rows that aren't merged (but are associated to the merged cells in that row.... sorry, not very clear...) You will see what I mean on the spread sheet hopefully.

The gold standard would be to have a button which you could click to run the macro and hide the rows, then click again (or a second button) to unhide them again...

Any help would be very gratefully received.

A
Conditional Row Hide.xlsx

Hello. New user here.
I'm having a problem with a simple macro to hide rows. Column A of the rows to be hidden contain check boxes. The macro can hide the rows, no problem, but one check box will not hide. All the other check boxes are hidden, along with their relative row. I've tried changing the format control properties, but nothing seems to work. How do get the macro to hide the check boxes along with the rows??

macro to hide rows:
VB:

Sub Hide_other_elec() 
    Rows("37:68").Hidden = True 
End Sub 
Sub Unhide_other_elec() 
    Rows("37:68").Hidden = False 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Thank you!

There it is again:
This one to hide rows with "info" in column B:

Dim cell As Range
For Each cell In Range("B:B")
If cell = "i" Then Rows(cell.Row).Hidden = True
Next

(Work fine...)

And this one to show them back:

Rows("1:100").Select
Selection.EntireRow.Hidden = False

However, the "show all" button doesn't work once I hit the "hide info" button...

Please help! Can any1 tell me why?


Hi,
I'm a beginner and need some help....

How would I write a macro to hide a range of rows If a cell value is zero, then do the same for five additional ranges of rows?

In my words:
If AT214=0, hide rows 214 to 244
and
If AT245=0, hide rows 245 to 278
and
If AT279=0, hide rows 279 to 311
and
If AT312=0, hide rows 312 to 344
and
If AT345=0, hide rows 345 to 377
and
If AT378=0, hide rows 378 to 410

Any help to put me on the right path would be greatly appreciated!
Ben
Brisbane Austraila


I've come close to finding the answer, but seem to keep falling short. Here's what I'm trying to accomplish:

Column A (rows 14-35) will contain either "Y" or "N" or no response.
If a user selects "N", I want the row to be automatically hidden.

Autofilter doesn't "automatically" hide the row. I need to "reset" the autofilter and redefine the conditions in order for autofilter to hide the rows. I want the rows to be hidden instantly.

Any way to accomplish this? Thanks...


I have to make a list of people who will be getting charity baskets from our organization

I first list people who are nominated on sheet 1
on sheet 2 I mark with an "X" in column A if they will not be getting a basket

Sheet 3 picks up anyone who does not have an X in column A
The rows with in "X" on sheet 2 will be blank on sheet 3 (actually have a formula putting "" into any cell with an "X" in column A of sheet 2

On sheet 4 I want those blank rows to be hidden

I know, I probably have unnecessary sheets on this spreadsheet, but I want each step to be documented as to what occured.

Is there anyway to do this?

I will be making mailing labels from the list---either I hide the blank rows, or get quite a few blank mailing labels after I merge

thank you for your help


Currently, I have over a hundred worksheets that I first unhide all rows and then call the Hide_Rows_Script see example code below (in column 20 I sum all numbers in row if (total=0) then I hide if not I don't hide) so the key to hide or not is if total=0 in row then hide. Hope this makes sense. This works fine except it takes a few minutes to run. I would like to get it running quicker -- is there a way to select the 0 zero rows without running a loop to find them? Looking for a quicker code for this? Thanks.

Worksheet wx4 example:
a1=1 b1=1 t1(column 20) = 2 -- don't hide row
a2=0 b1=0 t1(column 20) = 0 -- hide row
a3=2 b1=2 t1(column 20) = 4 -- don't hide row


Sub hiderow()
'This macro evaluates lines to determine rows to be hidden
Sheets("wx4").Select
' Unhide rows
Rows("6:200").Select
Selection.EntireRow.Hidden = False
'Call hide rows script to hide rows, parms are for x(row) and y(column) values
Hide_Rows_Script 6, 20
end sub

Sub Hide_Rows_Script(x As Single, y As Single)
'This routine will loop through row and hide zeros
Cells(x, y).Select
Do While Cells(x, y).Value ""
' Hide rows
If Cells(x, y).Value = 0 Then
Rows(x).Select
Selection.EntireRow.Hidden = True
End If
' Increment to next row
x = x + 1
Loop
End Sub


hello im trying to create a button to hide unsed rows, can anyone help? basically the sample below is what i want to do but it hides all the rows listed.. i want it to hide only unused rows in the rows selected if yes is selected and unhide them when no is selected... any help please...

Thank you

wannahide = MsgBox("hide rows", vbYesNo)
If wannahide = vbYes Then
Rows("5:28").Hidden = True
Else
Rows("5:28").Hidden = False
End If


Hi All,

I am wanting to hide particular rows based on a cells content (AM4).

If AM4 = 7
Show Column C:H
Hide Column I:AL

If AM4=8
Show Column I:N
Hide Column C:H & O:AL

If AM4=9
Show Column O:T
Hide Column C:N & U:AL

If AM4=10
Show Column U:Z
Hide Column C:T & AA:AL

If AM4=11
Show Column AA:AF
Hide Column C:Z & AG:AL

If AM4=12
Show Column AG:AL
Hide Column C:AF

Thanks
Eric


I am making a bidding program and I have a VBA code written to hide/unhide cells based on a certain cells value. So if we have 10 things to bid we'd type "10" into A5 and it would unhide 10 rows. The problem is that when we do bids often times we do multiple bids, add/change orders, and rarely end up with the same products that we initially bid. I have it so that we can hide/unhide rows based on how many products we bid.

What I'm trying to accomplish is to hide any empty rows, which would be caused by us initially bidding something out and then taking it off the bid, as well as unhiding any rows that have a bid on it.

For example, lets say we bid 10 things, they end up not wanting 3 of them, which in the bid program is in rows 3, 5, and 7. We want to be able to automatically hide those rows but keep rows 1, 2, 4, 6, 8, 9, 10.

Here is a portion of the code I have so far, should be plenty to get the idea of where I'm at. The example above would be as if I initally put "10" into A5, deleted rows 3, 5 and 7 with data still in all the other rows (so even though A5 has value 10 in there there would only be 7 rows in that range showing, and if I put "5" into A5 there'd still be showing 7 rows). I'm pretty new to VBA, infact this is my first project, I only have about 5 hours into it and I'm basically learning as I go (this is like 1/10th of the whole code that I am posting). I am pretty sure I could make a button in the spreadsheet that would manually run a macro to hide/unhide the rows that I want, which is fine and I will do it if this isn't possible and/or I have to completely start the code over, but I am trying to make using this sheet as easy and "idiot proof" as possible as there will be multiple people using it.

Also, if im doing this completely assbackwards to begin with (like I said this is basically my first project in VBA), please let me know what would've been the "correct" way to do it.

Thanks in advance!



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
If Target.Value = NoValue Then
Range("A6:L26").EntireRow.Hidden = True
End If

If Target.Value = 1 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L7").EntireRow.Hidden = False
End If

If Target.Value = 2 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L8").EntireRow.Hidden = False
End If

If Target.Value = 3 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L9").EntireRow.Hidden = False
End If

If Target.Value = 4 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L10").EntireRow.Hidden = False
End If

If Target.Value = 5 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L11").EntireRow.Hidden = False
End If

If Target.Value = 6 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L12").EntireRow.Hidden = False
End If

If Target.Value = 7 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L13").EntireRow.Hidden = False
End If

If Target.Value = 8 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L14").EntireRow.Hidden = False
End If

If Target.Value = 9 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L15").EntireRow.Hidden = False
End If

If Target.Value = 10 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L16").EntireRow.Hidden = False
End If

End If

End Sub







Hey, I'm having issue with getting my vba code to run correctly...here is my problem:

What I'm trying to do is hide certain rows in excel based on a formula result. I have a vlookup in excel which returns a number from 1 to 9. Based on different conditions that are met, it will return a number indicating which rows are needed to hide.

In my vba code, I have a case for each of the numbers 1 to 9, along with the commands to hide the necessary rows. The vlookup in excel seems to be working fine, and the number will change when different conditions are met. The problem I'm having is that when the number changes, it seems that this is not being picked up in the vba code. It will not hide the correct rows after the number has changed. However, if I go to excel and manually type in a different number, the macro will run correctly and hide the correct rows.

I'm not sure if this has made any sense so far. What I'm wondering is if there is a way to add code that will recognize when the number has changed and hide the associate rows for that number?

Thank you in advance and I apologize if this is not very clear.

Hi,

I have a spreadsheet with around 20+ seperate sheets. Approx half of these are little more than calculation sheets, whilst the remainder have been formatted into data entry and output sheets and are intended to be seen.

The question I have is that I have a number of embedded objects on the calculation sheets, e.g. Visio drawings etc. I want to hide all of the "calculation sheets" for security and to hide sensitive calculations.

On my main sheet, where all of the data is entered I have a number of macro buttons that will open these embedded documents, however once the calculation sheets are hidden these macros will no longer run. Do I have any alternative than to put the embedded objects onto a unhidden sheet? If I have to put them on a sheet that can be viewed, will I have the same issue if I hide the rows or columns that they are embedded at?

thanks in anticipation

I am using 2003

Pete


First, this is a GREAT forum. Obviously, everyone here is much more talented at Excel than I.

I am trying to construct a workbook that mainly consists of two sheets. The first would serve as a master sheet that allows me to mark "Y" or "N" for meetings and services. The next sheet would list agents and only the services and meetings marked with a "Y".

Altering a vba code that I read from someone else on this forum, I see how to hide rows with a "N" on the sheet I am 'working' from, but not sure how to transfer this over to the other sheet.

Below are my questions. Please refer to my attachment to better understand my situation. I thought that would be more helpful than listing rows numbers.

First button's questions:
1. How do I get a vba/macro to not only hide rows on this sheet, but also on the following sheet?

2. rows 4-11, 13-20, and 22-29, how would I get these to hide if their preceding row is "N"? For example, in this sheet, if "Meeting Name 3" is marked "N" how would 22-29 hide in this sheet?

3. Also, besides copying the "Y" or "N" value to the next sheet and performing an auto hide, is there a better way to do this (i.e., transfer only rows to the next sheet with a "Y" or assocaited w/ "Y" meetings)?

Second button's question:
1. I was going to record a macro to unhide all rows, but wanting to do this for both sheets, I wasn't sure if this was the best method to unhide all rows on both sheets.

THANKS!
Larry