• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Macro to unhide rows|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Macro to unhide rows|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosMacro to unhide rows
sp_PrintTopic sp_TopicIcon
Macro to unhide rows
Avatar
Nicole Danfakha

New Member
Members

Dashboards
Level 0
Forum Posts: 2
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
1
June 10, 2020 - 3:11 am
sp_Permalink sp_Print

Hello, I am creating a data entry form in Excel. I would like the number of tables under Section 3 to dynamically display based on a cell C8's value entered by a user. For example, if a user enters 3 in C8, then 3 spaces are available for them to enter data under section 3. If they enter 2, then 2 subsections are displayed (unhidden). I have a functioning macro to hide rows based on the cell's value. The macro works for values when a user enters 4 then changes 3 then 2. But it does not work if they first enter a lower number then change to a higher number since the macro only hides rows but does not unhide them. Ideally, they would enter the correct value on the first try but in case they need to update/change. 

 

Alternatively, if there is another easier way to do this, I am welcome to trying that. I have written code for 4 subsections but will need to add more and currently the code is being updated manually so I will need to write each IF statement for the number of potential subsections a user will need to fill out. 

Thanks in advance for assistance! The code is pasted below and the sample workbook is attached. 

 

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

If Target.Column = 5 And Target.Row = 8 Then
If Target.Value = "1" Then
Application.Rows("54:85").Select
Application.Selection.EntireRow.Hidden = True

ElseIf Target.Value = "3" Then
Application.Rows("75:85").Select
Application.Selection.EntireRow.Hidden = True

ElseIf Target.Value = "2" Then
Application.Rows("65:85").Select
Application.Selection.EntireRow.Hidden = True

ElseIf Target.Value = "4" Then
Application.Rows("86").Select
Application.Selection.EntireRow.Hidden = False

End If
End If

End Sub

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
June 10, 2020 - 6:56 am
sp_Permalink sp_Print

I am sure people on here will know a better way, but until then.......

If the number of Funding Sources is unlimited then i think you will need to get a loop going and create each instance rather than hiding/unhiding.

For example

Sub CreateRows()

Dim fSource As Integer 'number of funding sources
Dim I As Integer 'loops
Dim sRow As Integer ' row to start the input from

sRow = 11 ' starting row

fSource = Range("e8") 'number user typed in e8

'start loop
For I = 1 To fSource

Range("c" & sRow + I) = I 'create list of numbers

Range("e" & sRow + I).Interior.Color = vbYellow ' colour cell to show user

'create data validation in each cell
With Range("e" & sRow + I).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$O$2:$O$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Next I

're-sum start row for next inut part
sRow = sRow + fSource + 3

End Sub

The next part from 18 downwards would then carry on and re-sum your start row as sRow = sRow + fSource + 3 (blankrows), it can get a bit unweildy with the numbers but once correct it will be more flexible

Where the banners will be placed might be a little tricky but i would deal with that later, at the moment its is working out the calculations behind where the boxes go.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1550
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
3
June 10, 2020 - 2:36 pm
sp_Permalink sp_Print sp_EditHistory

Hi Nicole,

You could try the attached code which works for the 4 sections you currently have in the document.  If the maximum number of tables under Section 3 isn't too large you can easily expand this code to accommodate, but if you will have lots of tables then a loop in the code would probably be better as Purfleet said.

This code uses named ranges to hide the entire row where a table resides e.g. 

Range("Activity_Table_2").EntireRow.Hidden = True

and uses SELECT CASE which I find easier to read than lots of IF's 🙂

I've created 4 named ranges named (surprise!) Activity_Table_1, Activity_Table_2 etc.

These named ranges only need to refer to the first cell in each row as that's enough for the EntireRow method to work.

My code assumes that the first table will always be visible.  If it is not then you can hide it in the Worksheet_Activate event.

Regards

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: QSolutions Group
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
Hans Hallebeek: 185
Newest Members:
Kate Dyka
Kwaje Alfred Mogga
thong nguyen
Appiagyei Kofi Frimpong
Hilary Burchfield
Richie Wright
Adel Kock
Barbara Murray
Doug Milne
Siobhan Stringer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6547
Posts: 28656

 

Member Stats:
Guest Posters: 49
Members: 32832
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.