A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Automatically hide/unhide rows



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 12:20 PM posted to microsoft.public.excel.worksheet.functions
JStiehl
external usenet poster
 
Posts: 44
Default Automatically hide/unhide rows

I'm a VBA newbie and have searched for an answer for how to do this, but must
be doing something wrong when I've tried other codes. I need to have rows
automatically hide or unhide based on a value in column D. If the value in
column D is Y, the row should not be hidden; if the value is N, the row
should be hidden. These values may change, so the rows need to be able to
hide/unhide automatically.

When I've entered codes in before, I click on the tab for this worksheet,
select View Code, choose Insert Module, and then insert the code. There is
already a code in this worksheet for something else, so I can't just add it
in after selecting View Code. Am I putting the code in the wrong spot?
Should I be choosing something else other than Insert Module?

I've tried out so many different codes thinking that was the problem, but
now I think I may just be entering it wrong. I appreciate your help.
  #2  
Old April 23rd, 2010, 12:45 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Automatically hide/unhide rows

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then Rows(Target.Row).Hidden = (UCase(Target.Text) = "N")
End Sub

--
Jacob (MVP - Excel)


"JStiehl" wrote:

I'm a VBA newbie and have searched for an answer for how to do this, but must
be doing something wrong when I've tried other codes. I need to have rows
automatically hide or unhide based on a value in column D. If the value in
column D is Y, the row should not be hidden; if the value is N, the row
should be hidden. These values may change, so the rows need to be able to
hide/unhide automatically.

When I've entered codes in before, I click on the tab for this worksheet,
select View Code, choose Insert Module, and then insert the code. There is
already a code in this worksheet for something else, so I can't just add it
in after selecting View Code. Am I putting the code in the wrong spot?
Should I be choosing something else other than Insert Module?

I've tried out so many different codes thinking that was the problem, but
now I think I may just be entering it wrong. I appreciate your help.

  #3  
Old April 23rd, 2010, 01:24 PM posted to microsoft.public.excel.worksheet.functions
JStiehl
external usenet poster
 
Posts: 44
Default Automatically hide/unhide rows

I followed your directions and entered the code and there was no change. My
rows with an N in column D are not hidden.

"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then Rows(Target.Row).Hidden = (UCase(Target.Text) = "N")
End Sub

--
Jacob (MVP - Excel)


"JStiehl" wrote:

I'm a VBA newbie and have searched for an answer for how to do this, but must
be doing something wrong when I've tried other codes. I need to have rows
automatically hide or unhide based on a value in column D. If the value in
column D is Y, the row should not be hidden; if the value is N, the row
should be hidden. These values may change, so the rows need to be able to
hide/unhide automatically.

When I've entered codes in before, I click on the tab for this worksheet,
select View Code, choose Insert Module, and then insert the code. There is
already a code in this worksheet for something else, so I can't just add it
in after selecting View Code. Am I putting the code in the wrong spot?
Should I be choosing something else other than Insert Module?

I've tried out so many different codes thinking that was the problem, but
now I think I may just be entering it wrong. I appreciate your help.

  #4  
Old April 23rd, 2010, 01:40 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Automatically hide/unhide rows

The earlier code only works for new entries made....If you want a macro to
hide the existing entries try the below

--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run selected macro()


Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "D").End(xlUp).Row
Rows(lngRow).Hidden = (UCase(Range("D" & lngRow)) = "N")
Next
End Sub


--
Jacob (MVP - Excel)


"JStiehl" wrote:

I followed your directions and entered the code and there was no change. My
rows with an N in column D are not hidden.

"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then Rows(Target.Row).Hidden = (UCase(Target.Text) = "N")
End Sub

--
Jacob (MVP - Excel)


"JStiehl" wrote:

I'm a VBA newbie and have searched for an answer for how to do this, but must
be doing something wrong when I've tried other codes. I need to have rows
automatically hide or unhide based on a value in column D. If the value in
column D is Y, the row should not be hidden; if the value is N, the row
should be hidden. These values may change, so the rows need to be able to
hide/unhide automatically.

When I've entered codes in before, I click on the tab for this worksheet,
select View Code, choose Insert Module, and then insert the code. There is
already a code in this worksheet for something else, so I can't just add it
in after selecting View Code. Am I putting the code in the wrong spot?
Should I be choosing something else other than Insert Module?

I've tried out so many different codes thinking that was the problem, but
now I think I may just be entering it wrong. I appreciate your help.

  #5  
Old April 23rd, 2010, 02:04 PM posted to microsoft.public.excel.worksheet.functions
JStiehl
external usenet poster
 
Posts: 44
Default Automatically hide/unhide rows

Thanks so much for your help. I don't know what my problem is, but it still
isn't working. I pasted the code per your directions, but when I run the
macro "My Macro", nothing changes or happens.

"Jacob Skaria" wrote:

The earlier code only works for new entries made....If you want a macro to
hide the existing entries try the below

--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run selected macro()


Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "D").End(xlUp).Row
Rows(lngRow).Hidden = (UCase(Range("D" & lngRow)) = "N")
Next
End Sub


--
Jacob (MVP - Excel)


"JStiehl" wrote:

I followed your directions and entered the code and there was no change. My
rows with an N in column D are not hidden.

"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then Rows(Target.Row).Hidden = (UCase(Target.Text) = "N")
End Sub

--
Jacob (MVP - Excel)


"JStiehl" wrote:

I'm a VBA newbie and have searched for an answer for how to do this, but must
be doing something wrong when I've tried other codes. I need to have rows
automatically hide or unhide based on a value in column D. If the value in
column D is Y, the row should not be hidden; if the value is N, the row
should be hidden. These values may change, so the rows need to be able to
hide/unhide automatically.

When I've entered codes in before, I click on the tab for this worksheet,
select View Code, choose Insert Module, and then insert the code. There is
already a code in this worksheet for something else, so I can't just add it
in after selecting View Code. Am I putting the code in the wrong spot?
Should I be choosing something else other than Insert Module?

I've tried out so many different codes thinking that was the problem, but
now I think I may just be entering it wrong. I appreciate your help.

  #6  
Old April 23rd, 2010, 02:29 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Automatically hide/unhide rows

Set the Security level to low/medium in (Tools|Macro|Security) and reopen excel

--
Jacob (MVP - Excel)


"JStiehl" wrote:

Thanks so much for your help. I don't know what my problem is, but it still
isn't working. I pasted the code per your directions, but when I run the
macro "My Macro", nothing changes or happens.

"Jacob Skaria" wrote:

The earlier code only works for new entries made....If you want a macro to
hide the existing entries try the below

--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run selected macro()


Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "D").End(xlUp).Row
Rows(lngRow).Hidden = (UCase(Range("D" & lngRow)) = "N")
Next
End Sub


--
Jacob (MVP - Excel)


"JStiehl" wrote:

I followed your directions and entered the code and there was no change. My
rows with an N in column D are not hidden.

"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then Rows(Target.Row).Hidden = (UCase(Target.Text) = "N")
End Sub

--
Jacob (MVP - Excel)


"JStiehl" wrote:

I'm a VBA newbie and have searched for an answer for how to do this, but must
be doing something wrong when I've tried other codes. I need to have rows
automatically hide or unhide based on a value in column D. If the value in
column D is Y, the row should not be hidden; if the value is N, the row
should be hidden. These values may change, so the rows need to be able to
hide/unhide automatically.

When I've entered codes in before, I click on the tab for this worksheet,
select View Code, choose Insert Module, and then insert the code. There is
already a code in this worksheet for something else, so I can't just add it
in after selecting View Code. Am I putting the code in the wrong spot?
Should I be choosing something else other than Insert Module?

I've tried out so many different codes thinking that was the problem, but
now I think I may just be entering it wrong. I appreciate your help.

  #7  
Old April 26th, 2010, 01:29 PM posted to microsoft.public.excel.worksheet.functions
JStiehl
external usenet poster
 
Posts: 44
Default Automatically hide/unhide rows

The security level was set at medium, I changed it to low...it's still not
working when I run the macro. Any other ideas? Thanks so much for your help.

"Jacob Skaria" wrote:

Set the Security level to low/medium in (Tools|Macro|Security) and reopen excel

--
Jacob (MVP - Excel)


"JStiehl" wrote:

Thanks so much for your help. I don't know what my problem is, but it still
isn't working. I pasted the code per your directions, but when I run the
macro "My Macro", nothing changes or happens.

"Jacob Skaria" wrote:

The earlier code only works for new entries made....If you want a macro to
hide the existing entries try the below

--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run selected macro()


Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "D").End(xlUp).Row
Rows(lngRow).Hidden = (UCase(Range("D" & lngRow)) = "N")
Next
End Sub


--
Jacob (MVP - Excel)


"JStiehl" wrote:

I followed your directions and entered the code and there was no change. My
rows with an N in column D are not hidden.

"Jacob Skaria" wrote:

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then Rows(Target.Row).Hidden = (UCase(Target.Text) = "N")
End Sub

--
Jacob (MVP - Excel)


"JStiehl" wrote:

I'm a VBA newbie and have searched for an answer for how to do this, but must
be doing something wrong when I've tried other codes. I need to have rows
automatically hide or unhide based on a value in column D. If the value in
column D is Y, the row should not be hidden; if the value is N, the row
should be hidden. These values may change, so the rows need to be able to
hide/unhide automatically.

When I've entered codes in before, I click on the tab for this worksheet,
select View Code, choose Insert Module, and then insert the code. There is
already a code in this worksheet for something else, so I can't just add it
in after selecting View Code. Am I putting the code in the wrong spot?
Should I be choosing something else other than Insert Module?

I've tried out so many different codes thinking that was the problem, but
now I think I may just be entering it wrong. I appreciate your help.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.