+ Reply to Thread
Results 1 to 7 of 7

Hide a range of rows based on cell values.

Hybrid View

  1. 08-13-2014, 09:08 AM #1
    klunker
    klunker is offline
    Registered User
    Join Date
    11-25-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Hide a range of rows based on cell values.

    Hi All,
    I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't. Any suggestions would be a great help. Thanks.
    Sub Button1_Click()
Dim starta As String
Dim finisha As String

starta = Sheets("Sheet1").Range("A1")
finisha = Sheets("Sheet1").Range("A2")

Sheets("Sheet2").Range("starta" & ":" & "finisha").EntireRow.Hidden = True
    
End Sub
    Reply With QuoteRegister To Reply
  2. 08-13-2014, 09:12 AM #2
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide a range of rows based on cell values.

    Perhaps 
    Sheets("Sheet2").Range("A" & Sheets("Sheet1").Range("A1").Value & ":A" & Sheets("Sheet1").Range("A2").Value).EntireRow.Hidden = True
    This should hide it. If the values are changed do the previously hidden rows need to be unhidden before the new values are hidden?
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."
    Reply With QuoteRegister To Reply
  3. 08-13-2014, 09:14 AM #3
    patel45
    patel45 is offline
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Hide a range of rows based on cell values. 

    Sub Button1_Click()
Dim starta As String
Dim finisha As String

starta = Sheets("Sheet1").Range("A1")
finisha = Sheets("Sheet1").Range("A2")

Sheets("Sheet2").Range("A" & starta" & ":" & "A" & finisha).EntireRow.Hidden = True
    
End Sub
    If solved remember to mark Thread as solved
    Reply With QuoteRegister To Reply
  4. 08-13-2014, 10:23 AM #4
    klunker
    klunker is offline
    Registered User
    Join Date
    11-25-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Hide a range of rows based on cell values.

    Thanks patel45. Both yours and Solus's fixes work great!
    Reply With QuoteRegister To Reply
  5. 08-13-2014, 09:18 AM #5
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide a range of rows based on cell values.

    Here is another option if you would like the previous hidden rows unhidden first 
    Sheets("Sheet2").UsedRange.EntireRow.Hidden = False
Sheets("Sheet2").Range("A" & Sheets("Sheet1").Range("A1").Value & ":A" & Sheets("Sheet1").Range("A2").Value).EntireRow.Hidden = True
    Reply With QuoteRegister To Reply
  6. 08-13-2014, 10:23 AM #6
    klunker
    klunker is offline
    Registered User
    Join Date
    11-25-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Hide a range of rows based on cell values.

    Thanks Solus for the quick and very helpful reply! I had a feeling it had to do with my formatting of the "True" line.
    Reply With QuoteRegister To Reply
  7. 08-13-2014, 10:26 AM #7
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide a range of rows based on cell values.

    Glad to help.

    If you are satisfied with the solution please mark the thead as [SOLVED] using the thread tools at the top.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] First unhide all rows - then hide rows based on specific cell value for a range of cells
    By robbiekh in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-22-2013, 05:46 PM
  2. Need help to show/hide rows based on values from a cell
    By concurmgr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-19-2012, 02:56 PM
  3. [SOLVED] How to hide rows based on cell value, row range is dynamic.
    By csh8428 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2012, 02:05 PM
  4. Hide Rows based on cell values
    By Chinny123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2011, 08:45 AM
  5. Hide rows based on cell values
    By mlb830 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-18-2009, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1