+ Reply to Thread
Results 1 to 3 of 3

Button hides lines, if lines are added or removed, code is irrelevant

  1. #1
    Registered User
    Join Date
    11-25-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Button hides lines, if lines are added or removed, code is irrelevant

    Hi everyone,

    I have added some buttons in a worksheet that hide certain rows if they are not necessary.

    My issue is that if rows are added or removed from the Range that the code touches, then my code doesn't adjust itself which makes it irrelevant.

    Is there a way that I could make it so the rows indicated in the code are tied to a start and end point so my table is hidden and shown as desired?

    Here is my code :



    Private Sub ToggleButton1_Click()
    Dim xAddress As String
    xAddress = "58:64"
    If ToggleButton1.Value Then
    Application.ActiveSheet.Rows(xAddress).Hidden = True
    Else
    Application.ActiveSheet.Rows(xAddress).Hidden = False
    End If
    End Sub



    Picture of file as is when hidden :

    Capture.PNG

    Picture of file as is when not hidden :

    Capture.PNG

    Picture of file if rows are added and then the button is used to hide the table (2 rows were added at the center of the table) :

    Capture.PNG

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Button hides lines, if lines are added or removed, code is irrelevant

    Select Rows 58:64 and give them a Named Range
    Excel Names - Named Ranges

    When you insert rows within a named range, it automatically adjusts to include the new row.

    Then reference the named range (called MyRange for this example) in your code.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    11-25-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Re: Button hides lines, if lines are added or removed, code is irrelevant

    You are a god send. This works too well. Thanks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. WRAP text (show last added lines/hide first lines)
    By Jeppe88hansen in forum Outlook Formatting & Functions
    Replies: 3
    Last Post: 01-08-2018, 08:58 PM
  2. Replies: 1
    Last Post: 01-03-2018, 01:40 PM
  3. Multiple Lines added as Macro button
    By benbaker9876 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2013, 07:01 AM
  4. Replies: 0
    Last Post: 06-21-2011, 12:47 PM
  5. Counting pages and lines (added issue)
    By adventuresofgabe in forum Excel General
    Replies: 6
    Last Post: 04-21-2011, 10:58 AM
  6. Replies: 3
    Last Post: 04-27-2007, 11:40 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1