+ Reply to Thread
Results 1 to 2 of 2

Shifting up rows to eliminate blank rows in real time on second printable sheet

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    FL
    MS-Off Ver
    2013
    Posts
    4

    Shifting up rows to eliminate blank rows in real time on second printable sheet

    I have a form that I fill out all the time, where a few basic numbers are taken, some calculations are made, and results are spit out onto another worksheet which is formatted for printing.

    My problem is often I don't need certain rows so I'm left with awkward gaps in the spreadsheet that have to be manually deleted before I print. It's not a big deal but something I have to do all the time.

    Is there a way (formula or VBA, I would prefer formula but I took programming back in college so I know the basics though I've never done VBA) which I can shift up rows so that when I print only the filled rows are shown without a gap in between them (leaving more blank space on the bottom of the page). This ideally needs to be done in real time so that I can click over to my printable sheet and ctrl+p without having to adjust any spacing

    Essentially I'm taking several inputs, automatically generating a sheet which has several blanks, then outputting it to a pretty printable worksheet which won't have any blank rows, but will still have the bottom rows in the same place.


    BEFORE:
    Data 1 123
    BLANK BLANK
    Data 12
    BLANK BLANK
    Data 12

    AFTER:
    Data 1 123
    Data 12
    Data 12

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,412

    Re: Shifting up rows to eliminate blank rows in real time on second printable sheet

    I suggest you create a 2nd table, which references the 1st table, then priont the 2nd table...

    A
    B
    1
    2
    Data 1 123
    3
    4
    Data 12
    5
    6
    Data 12
    7
    8
    AFTER:
    9
    Data 1 123
    10
    Data 12
    11
    Data 12

    A9=IFERROR(INDEX(A$2:A$7,SMALL(IF($A$2:$A$7>"",ROW($A$2:$A$7)-1),ROWS($A$1:A1))),"")
    This is an ARRAY formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. SumIF and eliminate blank rows
    By jkktx81 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-06-2015, 07:34 PM
  2. [SOLVED] Eliminate blank rows when pulling criteria based data from one spreadsheet to another
    By Terisammis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-28-2014, 10:39 AM
  3. Replies: 7
    Last Post: 02-01-2014, 01:48 AM
  4. Replies: 4
    Last Post: 04-24-2013, 04:58 AM
  5. Eliminate blank rows by shifting rows up
    By jman0707 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-06-2008, 02:10 AM

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