Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA to remove rows when cell in a column is blank

    Hi experts,

    I want to setup a spreadsheet with pre-set graphs and calculations (with 1000 rows only). User only allow to enter 2 types of info: 1) Data ID (Row A), 2) Data Value (Row B).

    Since this spreadsheet will be locked (except the 2 columns), user cannot delete un-used rows (Eg. Max with 1000 rows, but user may need 200 entries only).

    I need the user to be able to "hide" or "delete" blank rows, so that the graph axis will reflect only used rows. Eg. 200 entries, not 1000 entries.

    Tried to use various equations but getting very complicated. Is there any macro can help me to resolved this issue?
    Many thx all.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,503
    Thanks
    212
    Thanked 853 Times in 785 Posts
    kchan,

    Welcome to the Lounge as a new poster!

    I'm not sure you need a macro. What may serve your needs better is the use of Dynamic Range Names. A Dynamic Range Name automatically adjusts to the amount of used rows in the worksheet. As long as the rows are consecutive this approach should work. Once you create the Dynamic Range Names you will use them in the definition of your graph.

    Of course I'm assuming that all data is input from top to bottom at each use. If this is the case Dynamic Range Names should fill the bill.

    Create the Range Names you need for the graph using the following as the Refers to: value.

    =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A$2:$A$1001),1)

    Note: You'll need to adjust the formula by replacing Data with your sheet name and adjusting the 1st Range as appropriate for each range necessary for the graph. Leave the CountA part alone since all the range names will use the same number of rows and that is what the CountA part calculates.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    kchan133 (2014-05-25)

  4. #3
    New Lounger
    Join Date
    May 2014
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    THANK YOU SIR!!! I tried dynamic range before and most likely, I did not setup correctly! Thanks a lot for the last note which allows me to set it up properly this time.
    So glad to have an expert willing to give amateur great guidance! Thx for sharing - very much appreciated!D

Posting Permissions

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