Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2014
    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. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 781 Times in 715 Posts

    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.


    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.

    May the Forces of good computing be with you!


    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs

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

    kchan133 (2014-05-25)

  5. #3
    New Lounger
    Join Date
    May 2014
    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