+ Reply to Thread
Results 1 to 5 of 5

Minor fix to existing macro - bottom borders not being generated properly

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Minor fix to existing macro - bottom borders not being generated properly

    Hi all,

    I'm having an issue with the borders generated by a simple macro that takes a spreadsheet with alternating shaded sets of rows, and creates a border everytime the shading colour changes. The core functionality of the macro is working fine, but it does not shade the bottom of cells in the 1st and last column of the bottom row on each page, or the bottom of the cells in the 1st and last column of the repeating header, when the document is converted to a PDF (this can be seen in Page Layout view after the macro is run)

    I've attached a copy of a spreadsheet prior to running the macro (ie. shaded cells without the borders added yet), as well as the macro itself (code is below, it's also embedded in the file as "ShadingtoBorders"). If you run the macro on this spreadsheet, you'll see the problem appear.

    I imagine this must be a simple fix, but it's beyond my talents. Thanks for any help you can provide.


    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Minor fix to existing macro - bottom borders not being generated properly

    I see what you're talking about on the print preview. I know that print previews usually can't be trusted to show borders accurately, and that it's usually fine on the actual printout. I don't know about pdf's. How does it look on an actual printout?

    Also, I would suggest adding
    Please Login or Register  to view this content.
    at the beginning of your subroutine. Not only will it eliminate all the annoying screen flashing while your code runs, but it will reduce the run time (at least when I ran it) from 6 seconds to about half a second.
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    04-25-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Minor fix to existing macro - bottom borders not being generated properly

    I actually only care about converting to a PDF, as this spreadsheet is never printed as a hard copy. The issue holds whether I print to a PDF printer, or use the Save As PDF function within Excel 2010.

    The problem is definitely that the bottom borders not being established properly, as I can manually update (remove and re-instate) the bottom border for each problem row, and then things are fine. ...but that's a lot of wasted time that the macro should be able to eliminate.

    Good call on the screenupdating. Thanks.

  4. #4
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Minor fix to existing macro - bottom borders not being generated properly

    I found that it's a problem on the printed page as well. I don't think it has anything to do with your code. It has to do with Excel being wacky. I was about to tell you "tough luck", but then I noticed what you said about it working if you manually updated the border, so I started to do some experimenting. It turns out that if you reset the border value to its own value (xlmedium), the problem inexplicably goes away. If you put the following code at the bottom of your routine, it appears to fix it. I can't explain why.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-25-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Minor fix to existing macro - bottom borders not being generated properly

    That's excellent! It definitely solves the problem. Thanks so much for your help.

+ 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. Replies: 1
    Last Post: 02-26-2013, 05:56 PM
  2. Minor modifications of existing script
    By Rawland Hustle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2012, 12:08 PM
  3. Put borders at top and bottom of each row, but not sides?
    By Moonbather in forum Excel General
    Replies: 2
    Last Post: 08-04-2012, 02:31 PM
  4. [SOLVED] bottom borders in a For Loop
    By CJ in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-14-2006, 10:30 PM

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