+ Reply to Thread
Results 1 to 6 of 6

Trimming empty cells that are not considered empty or blank because they have a formula??

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Trimming empty cells that are not considered empty or blank because they have a formula??

    I am trying to code a basic excel file (but if anyone can make an actual basic program and charge me $300) to essentially auto-write operation reports for a plastic surgeon. The reports consist of 3 parts (plus a header/footer with the patient and doctors names). The risks paragraphs before the patient has anything done and what COULD HAPPEN that they discussed, the anesthesia type they were given for the operation, and then the actual operation procedure that was done.
    So if the patient has a nosejob and their pecs augmented, the operation report would say a few paragraphs about the risks discussed in getting a nosejob, then it would talk about the risks of getting pectoral augmentations. THEN, it would say they were put under general anesthesia or whatever type they were given. THEN, it would say a few paragraphs about how the nosejob was actually completed, and then a few paragraphs about the augmentation.

    There are a total of about 30 different generic choices (plus 4 anesthesia types) which coincide with 30 different paragraph sets at the first half of the report, and likewise 30 different paragraph sets at the second half. A patient may have anywhere from 1 to 5 procedures or so, which then equals 1-5 sets of writings, a paragraph on the anesthesia ,and then 1-5 sets of what actually happened.

    As of now, my excel file is about 90 percent done and I am stuck. I basically wrote a set of IF STATEMENTS to where if a 1 is put in the cell next to which procedures were going to be had and done, they pull data from some other cells and put them into cells on a new worksheet. Because there are 30 different options or so, there may be up to 29 blank cells in between the paragraphs, i.e. if they had the first procedure to choose from and also the last one. i.e. cells A2 to A29 would be empty.
    Here’s the problem, I want to copy and paste the final excel document that populated the paragraphs but with blank cells that translates to 29 or more blank lines on a Microsoft word document that have to be deleted. How do I make the empty cells go away before I do a copy and paste? I tried a hide function, but that would just hide data from the cell but the cell still equals one more line I have to erase. I tried a trim function on blank cells but they aren’t REALLY blank because they have a formula for the if statements in them that just hadn’t been activated for this patient. Any thoughts? Much appreciated!!

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Trimming empty cells that are not considered empty or blank because they have a formul

    It seems to me that you could use a column to flag the rows to be hidden.
    Example:
    A1: Flag
    A2: =IF(C2="","HIDE","SHOW")

    Copy that formula down as far as you need...assuming through A50

    Then
    • Select A1:A50
    • Data.Filter
    • Click the dropdown and select SHOW...that will hide the "HIDE" rows.
    • Copy the visible cells you need and paste them into MS Word.

    When done...set the filter dropdown to All.

    Does that work for you?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Trimming empty cells that are not considered empty or blank because they have a formul

    Hi Daniel and welcome to the forum,

    If I was doing this problem I'd be looking at using Excel and Word combination Mail Merge.

    There are a lot of online tutorials like http://mac2.microsoft.com/help/offic...4-c3c34ce584e7 or http://www.internet4classrooms.com/m...formletter.htm

    But you want to have full paragraphs that are substituted into a form letter.

    Search a little and do some examples to discover how to insert entire paragraphs in the Mail Merge process. Perhaps http://www.youtube.com/watch?v=UusH-4DvFaw shows. it...
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    01-11-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Trimming empty cells that are not considered empty or blank because they have a formul

    Hey thanks! I didn't know about a hide sort type feature... I did learn how to hide worksheets recently (actually now ironically I haven't found a way to bring them back but I'm sure thats a quick google) so it'll probably work. Since I am already "flagging" segments of sorts I wonder if there's a way to work in your code to what I've test written... would you be so kind as to revise your example cells based on this here attachment? Wasn't sure how we went from A to C while skipping B
    Attached Files Attached Files

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Trimming empty cells that are not considered empty or blank because they have a formul

    I attached an edited version of your workbook.
    These are the changes:
    On the FinalReport sheet
    • Replace " " in formulas with ""...that will create a text-blank.
    • Insert a row at Row_1 for headings
    • A1: Notes
    • B1: Flag
    • B2: =IF(A2="","HIDE","SHOW")
    • Copy B2 down through B13
    • Select A1:B13
    • Data.Filter...that will create dropdown arrows in Row_1
    • Click the Flag dropdown and UNcheck Hide
    Now only the active notes will be visible

    Does that help?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-11-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Trimming empty cells that are not considered empty or blank because they have a formul

    Yes excellent thanks very very much. Please let me know if there's any smilies or props or kudos or "issue solved"s I can send your way

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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