Hide/Unhide Rows Problem

J

jm485

New Member
Joined
Mar 7, 2011
Messages
12
I have two forms. One form has all the userform pop ups asking for details of expenses made (Expense Report P-1) Another form has all the details listed so it can be printed out (Expense Report P-2)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
On P-2 report I created a macro that would hid the rows if a certain cell on the page was < = 0 and would unhide if the same cell was > 0 it will also lock the cells so changes can't be made when there is a value >0 in the certain cell and will unlock when the certain cell's value is <0
<o:p></o:p>
<o:p></o:p>
The codes I have created work fine for 6 categories each referencing 25 days but the last two categories and their 25 days only allow one row to show at a time. So if for transportation day 1 details are filted in from a userform on P-1 and the cell G278 on P-2 shows a value then it the row (278:278) will unhide. And will hide again when the cell G278's value is 0.

However, if details are also filtered in from a form for transportation day 2 and the cell G279 shows a value it will unhide but then the row for day 1 will hide. If I delete the value from cell G278 or G279 they both will hide even though when I manually unhide the rows the value for the one I didn't delete is still showing.
<o:p></o:p>
<o:p></o:p>
I checked the codes for transportation with those that works and it all looks the same. I don't understand why this is happening.

(I am using Excel 2003)
<o:p></o:p>
<o:p></o:p>
Here is the code for the category Misc day 25 that works fine<o:p></o:p>
<o:p></o:p>
Rich (BB code): 
Sub HideRow_Misc25()<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-2").Activate<o:p></o:p>
If Range("G277").Value > 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("misc25,misc25a").Select<o:p></o:p>
Selection.Locked = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("277:277").Select<o:p></o:p>
Range("B277").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("misc25b").Select<o:p></o:p>
<o:p></o:p>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("G277").Value <= 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("misc25,misc25a").Select<o:p></o:p>
Selection.Locked = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("277:277").Select<o:p></o:p>
Range("B277").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("misc25b").Select<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Here are two codes for transportation day 1 and day 2 which are not working like the <o:p></o:p>
others do. <o:p></o:p>
<o:p></o:p>
Rich (BB code): 
Sub HideRow_Transportation1()<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-2").Activate<o:p></o:p>
If Range("G278").Value > 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab1,Rail1,Toll1,Gas1,Parking1,transport1a").Select<o:p></o:p>
Selection.Locked = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("278:278").Select<o:p></o:p>
Range("B278").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation1").Select<o:p></o:p>
<o:p></o:p>
<st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("G278").Value <= 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab1,Rail1,Toll1,Gas1,Parking1,transport1a").Select<o:p></o:p>
Selection.Locked = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("278:278").Select<o:p></o:p>
Range("B278").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation1").Select<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Rich (BB code): 
Sub HideRow_Transportation2()<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-2").Activate<o:p></o:p>
If Range("G279").Value > 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab2,Rail2,Toll2,Gas2,Parking2,transport2a").Select<o:p></o:p>
Selection.Locked = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("279:279").Select<o:p></o:p>
Range("B279").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation2").Select<o:p></o:p>
<o:p></o:p>
<st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("G279").Value <= 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab2,Rail2,Toll2,Gas2,Parking2,transport2a").Select<o:p></o:p>
Selection.Locked = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("279:279").Select<o:p></o:p>
Range("B279").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation2").Select<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
I eventually figured this out. It took forever! I changed the Expense Report P-1 to say Sheet1 and Expense Report P-2 to say Sheet2. That seemed to help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

itsgrady
  • Solved
I would like to unhide the row after printing
Replies
4
Views
320
itsgrady
itsgrady
R
  • Solved
Unhide next row in multiple column
2
Replies
12
Views
432
kevin9999
K
K
  • Question
Hide/Unhide Rows based on cell values
Replies
2
Views
1K
Alex Blakenburg
A
K
  • Question
Problem With Table References
Replies
0
Views
335
KAELondon
K
J
  • Question
VBA Code to Add Rows is slowing down
Replies
5
Views
567
JayB0730
J

Forum statistics

Threads
1,216,028
Messages
6,128,395
Members
449,446
Latest member
CodeCybear

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top