+ Reply to Thread
Results 1 to 6 of 6

Automattically adjust code when rows or columns are added

  1. #1
    Registered User
    Join Date
    03-13-2012
    Location
    WI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Automattically adjust code when rows or columns are added

    Hi!

    I'm seeking to get the following; I have a code for when a checkbox is checked or unchecked; rows hide and unhide. However; when I add a row somewhere in the spreadsheet, the code doesn't automaticcaly adjust. I have the following code;

    Please Login or Register  to view this content.
    So when I add a row; row 47 becomes 48 for instance. This way the code hides the wrong cell!

    Does anybody have any ideas on how to make the code automatically adjust accordingly??

    Would be much appreciated!
    Last edited by Tulipo; 04-04-2012 at 08:24 AM.

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Automattically adjust code when rows or columns are added

    Hi Tulipo
    Try using Named Ranges...it works for more than one row but will NOT work for single row ranges (65:65). For example
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    03-13-2012
    Location
    WI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Automattically adjust code when rows or columns are added

    Can anyone assist please?

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Automattically adjust code when rows or columns are added

    Hi Tulipo
    Have you tried Named Ranges...did it not work for you?

  5. #5
    Registered User
    Join Date
    03-13-2012
    Location
    WI, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Automattically adjust code when rows or columns are added

    Thanks for your reply. I don't think I understand what you mean by Named Ranges!

    Could you explain a little more what you mean?

    Thanks!

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Automattically adjust code when rows or columns are added

    If you create a Defined Name for a range, whenever you insert or delete rows the Defined Name will update automatically to include the new row positions. To do so:

    On the Formulas ribbon click Define Name. Next to 'Name' type a name for the range, e.g. myrange. Next to 'Refers to:' type (or copy/paste) the following formula:

    =Sheet1!$47:$54,Sheet1!$65:$65,Sheet1!$67:$70,Sheet1!$82:$93,Sheet1!$96:$116,Sheet1!$118:$119,Sheet1!$125:$131,Sheet1!$134:$138,Sheet1!$146:$147

    Click OK. You can then change your code to the following, and it should adjust when you insert or delete rows.
    Please Login or Register  to view this content.
    Change the sheet name as necessary.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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