I want to hide and unhide rows using VBA codes. I have a situation where i have a list of countries in the top (given in a dropdown) and i have some information corresponding to each country below. Now i want to see only the data for the country i have selected on the top and the data for all other countries should get hidden. Please help me on this. I am using excel 2010
Hiding and Unhiding rows using VBA code
- Thread starter jkveerya
- Start date
vijaySharma
Member
@jkveerya,
Welcome to Chando
rg Forums.
Start the VBEditor
Double Click on ThisWorkBook inside the Project Explorer
Now Paste the Below Code
Save and Close Your File and then Reopen...
Everytime you make a new selection of Country name from the drop-down this code will execute and set the hidden rows as desired.
HTH
~VijaySharma
Welcome to Chando
rg Forums.Start the VBEditor
Double Click on ThisWorkBook inside the Project Explorer
Now Paste the Below Code
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "YourSheetName" And Target.Address = "$A$1" Then
Select Case Target.Value
Case Is = "Country1"
'Hide what you want to hide
Rows("1:10").EntireRow.Hidden = True
'Show the others
Rows("10:20").EntireRow.Hidden = False
Case Is = "Country1"
Case Is = "Country1"
Case Is = "Country1"
Case Else
End Select
End If
End SubSave and Close Your File and then Reopen...
Everytime you make a new selection of Country name from the drop-down this code will execute and set the hidden rows as desired.
HTH
~VijaySharma
Thanks for your reply Vijay, But unfortunately i am not able to get this. To explain you the details, please refer to the below example
On top of the excel file i have the list of countries, where i can select from dropdown
If i select country 1, only details about country 1 should be visible and similary it should work for what ever country I select.
Select Country: Country 1, Country 2, Country 3, ......
Country Parameters 2007 2008 2009 2010 2011
Country 1 Country name
Country 1 Population
Country 1 GDP
Country 1 Healthcare spend
Country 1 Birthrate
Country 1 death rate
Country 1 Male population
Country 1 Female population
Country 2 Country name
Country 2 Population
Country 2 GDP
Country 2 Healthcare spend
Country 2 Birthrate
Country 2 death rate
Country 2 Male population
Country 2 Female population
Country 3 Country name
Country 3 Population
Country 3 GDP
Country 3 Healthcare spend
Country 3 Birthrate
Country 3 death rate
Country 3 Male population
Country 3 Female population
I am very new to using these VBA codes, please explain in detail for my better understanding. Thanks in advance for your support
On top of the excel file i have the list of countries, where i can select from dropdown
If i select country 1, only details about country 1 should be visible and similary it should work for what ever country I select.
Select Country: Country 1, Country 2, Country 3, ......
Country Parameters 2007 2008 2009 2010 2011
Country 1 Country name
Country 1 Population
Country 1 GDP
Country 1 Healthcare spend
Country 1 Birthrate
Country 1 death rate
Country 1 Male population
Country 1 Female population
Country 2 Country name
Country 2 Population
Country 2 GDP
Country 2 Healthcare spend
Country 2 Birthrate
Country 2 death rate
Country 2 Male population
Country 2 Female population
Country 3 Country name
Country 3 Population
Country 3 GDP
Country 3 Healthcare spend
Country 3 Birthrate
Country 3 death rate
Country 3 Male population
Country 3 Female population
I am very new to using these VBA codes, please explain in detail for my better understanding. Thanks in advance for your support
vijaySharma
Member
jkveerya,
The code I have sent is going to do exactly the same.
Can you upload you file on Google Docs OR SkyDrive so that I can download.
~VijaySharma
The code I have sent is going to do exactly the same.
Can you upload you file on Google Docs OR SkyDrive so that I can download.
~VijaySharma
NARAYANK991
Excel Ninja
Hi Veerya ,
Check out the worksheet at the following link :
https://docs.google.com/open?id=0B0KMpuzr3MTVQ0NJMkxnbm5SMUNCT0tVNmtsQWE2UQ
Narayan
Check out the worksheet at the following link :
https://docs.google.com/open?id=0B0KMpuzr3MTVQ0NJMkxnbm5SMUNCT0tVNmtsQWE2UQ
Narayan
NARAYANK991
Excel Ninja
Hi Veerya ,
You can see that one named range called Data_Range has been defined ; just extend the address of this named range to cover your data.
Secondly , extend your Data Validation list to cover all the 60 countries.
I don't think anything more is required.
Narayan
You can see that one named range called Data_Range has been defined ; just extend the address of this named range to cover your data.
Secondly , extend your Data Validation list to cover all the 60 countries.
I don't think anything more is required.
Narayan
NARAYANK991
Excel Ninja
Hi Veerya ,
Thanks for the feedback. Keep visiting.
Narayan
Thanks for the feedback. Keep visiting.
Narayan
Hi Narayan,
I am also looking for a macro code to hide the rows, which is almost similar to what Veerya asked. I haved copied the code from your previous posted file and pasted to in to the my excel work book and changed the ranges accordingly. However, it is not running in my work book and it is giving the debug and highlights the following sentence in yellow color
"Range("Data_Range").EntireRow.Hidden = True".
I also uploaded the file in the google docs for your view and the path is given below. In this file, If I select one country from the drop down box then the corresponding country data should display and hide the rest of the rows.Each country has the data in the range of around 110 rows.
https://docs.google.com/open?id=0B_cFem0ElVX1SnlmZ0NGbWRURW1uV19ESUxyZEtmZw
Thanks in advance for your help.
I am also looking for a macro code to hide the rows, which is almost similar to what Veerya asked. I haved copied the code from your previous posted file and pasted to in to the my excel work book and changed the ranges accordingly. However, it is not running in my work book and it is giving the debug and highlights the following sentence in yellow color
"Range("Data_Range").EntireRow.Hidden = True".
I also uploaded the file in the google docs for your view and the path is given below. In this file, If I select one country from the drop down box then the corresponding country data should display and hide the rest of the rows.Each country has the data in the range of around 110 rows.
https://docs.google.com/open?id=0B_cFem0ElVX1SnlmZ0NGbWRURW1uV19ESUxyZEtmZw
Thanks in advance for your help.
NARAYANK991
Excel Ninja
Hi pkd37223 ,
Please find your worksheet at this link :
https://docs.google.com/open?id=0B0KMpuzr3MTVWTZxNGxhaTZTdnFhOWhsVUNfUlpOQQ
You needed to define the named range "Data_Range" by going to Formulas -> Name Manager and adding a new range named "Data_Range" referring to the entire data range relevant to your data , in your case =Sheet1!$A$7:$J$210
Narayan
Please find your worksheet at this link :
https://docs.google.com/open?id=0B0KMpuzr3MTVWTZxNGxhaTZTdnFhOWhsVUNfUlpOQQ
You needed to define the named range "Data_Range" by going to Formulas -> Name Manager and adding a new range named "Data_Range" referring to the entire data range relevant to your data , in your case =Sheet1!$A$7:$J$210
Narayan
Thanks a lot. It's working fine now.
I need one more small clarification. Now,I am trying to enter the data by selecting one country from dropdown box. The moment I enter the data in one cell then all country rows are getting hide.
For E.g. in the file you forwarded to me, I have selected the Canada from the dropdown and entered the data in the cell # D113. After pressing the enter all the rows got hide. Can you please help on this. The basic idea is after the selecting the one country from the drop down box I need to the enter the data in all the cells which are belongs to that particular country
Once again thanks for your help on this.
I need one more small clarification. Now,I am trying to enter the data by selecting one country from dropdown box. The moment I enter the data in one cell then all country rows are getting hide.
For E.g. in the file you forwarded to me, I have selected the Canada from the dropdown and entered the data in the cell # D113. After pressing the enter all the rows got hide. Can you please help on this. The basic idea is after the selecting the one country from the drop down box I need to the enter the data in all the cells which are belongs to that particular country
Once again thanks for your help on this.
NARAYANK991
Excel Ninja
Hi pkd37223 ,
Can you check the following worksheet ?
https://docs.google.com/open?id=0B0KMpuzr3MTVa1RoR2xhMzhUc3luSVp0WnFrTmxMQQ
Narayan
Can you check the following worksheet ?
https://docs.google.com/open?id=0B0KMpuzr3MTVa1RoR2xhMzhUc3luSVp0WnFrTmxMQQ
Narayan
Hi Narayan,
I am struggling to put this form together. This is quite a complex form and any advice will be great.
https://docs.google.com/open?id=0B_upMYdtf67RSlNvS0pDSWVoN3M
I think I have understood the logic of hiding rows based on list selection.However, I cant get it to work.
Tab 1 is the form and Tab 2 is the set of rules.
Look forward to hearing from you.
Rana
I am struggling to put this form together. This is quite a complex form and any advice will be great.
https://docs.google.com/open?id=0B_upMYdtf67RSlNvS0pDSWVoN3M
I think I have understood the logic of hiding rows based on list selection.However, I cant get it to work
.Tab 1 is the form and Tab 2 is the set of rules.
Look forward to hearing from you.
Rana
Hi
I am trying to do something similar to above but struggling to find the correct formulas. I have a 2 page spreadsheet with a list of products on one with prices and on the other, there is a quote template. I have linked the pages so that entering a value in the pricing page creates lines on the quote with the correct codes, descriptions and prices, but I need to use VBA to hide the lines that have a zero value. In other words, if a product on the price page has zero quantity then the corresponding line on the quote page is hidden. Can anyone help?
Dave
I am trying to do something similar to above but struggling to find the correct formulas. I have a 2 page spreadsheet with a list of products on one with prices and on the other, there is a quote template. I have linked the pages so that entering a value in the pricing page creates lines on the quote with the correct codes, descriptions and prices, but I need to use VBA to hide the lines that have a zero value. In other words, if a product on the price page has zero quantity then the corresponding line on the quote page is hidden. Can anyone help?
Dave
