+ Reply to Thread
Results 1 to 10 of 10

Loop Macro to Auto Format Borders

  1. #1
    Registered User
    Join Date
    08-19-2010
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    5

    Loop Macro to Auto Format Borders

    I am new to VBA and macros and have been looking around and trying to learn but there are several ways to do looping and I haven't grasped it yet. I am trying to create a loop that goes from cells A1-F1 until A#-F# where the next row is empty and apply borders to them all. What is the easiest way to do this? Thanks

  2. #2
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: Loop Macro to Auto Format Borders

    For many of the simple tasks, it is easy to record a macro when we manualy do it first. Then you can re-run the macro with the necessary changes. This is what I did for your requirement. See the code
    Please Login or Register  to view this content.
    rgds

    johnjohns

  3. #3
    Registered User
    Join Date
    08-19-2010
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Loop Macro to Auto Format Borders

    Thanks for the quick response. I did learn some from this post but I can't figure out how to modify the code to work for my application. I forgot to mention that cells A1-D1 through A#-D# are blank so I want to loop until column E is blank. Columns E and F will always contain data and end on the same row. Thanks

  4. #4
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: Loop Macro to Auto Format Borders

    For this requirement this is what I did.
    1 Started recording the macro
    2 Selected the cell F1
    3 Keeping the Shift Key pressed, striked End and Down Arrow Key
    so that I selected till the last non-empty cell (the one before the first empty cell) in F
    column
    4 Keeping the Shift Key pressed, striked the Home key so that I got
    the entire block including A-D columns
    5 Formatted the selected cells with borders
    6 Stopped recording the macro
    7 Here is the result
    Please Login or Register  to view this content.
    rgds

    johnjohns

  5. #5
    Registered User
    Join Date
    08-19-2010
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Loop Macro to Auto Format Borders

    Thanks for the explanation. That makes sense that you are selecting the most right column first and then selecting the left columns to then add borders. I have another question. I was wondering how I could add column G to get borders. Column G does not contain any data at all. So basically there are blank columns on the left and right of the columns that contain data.

  6. #6
    Registered User
    Join Date
    08-19-2010
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Loop Macro to Auto Format Borders

    I figured it out. I ended up just using column F but then selecting to the right so basically I do the format twice. Is there an easier way to do this?

  7. #7
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Loop Macro to Auto Format Borders

    Perhaps you could just copy formatting across from F for those cells with data?
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: Loop Macro to Auto Format Borders

    Also try

    Please Login or Register  to view this content.
    rgds

    johnjohns

  9. #9
    Registered User
    Join Date
    08-19-2010
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Loop Macro to Auto Format Borders

    Thanks for that more concise code. The code works perfectly. How would I go about adding a table a row below the last nonempty row. The problem I am having right now is that the actions that I am recording are set to a specific row number and the starting row of the table will change over time. I just don't know the proper key presses to cause the selection to go to the end of the data and then two more rows.

  10. #10
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Loop Macro to Auto Format Borders

    If you've got data in column A this will insert borders around a block of cells 5x3 two rows below the data.
    Please Login or Register  to view this content.

+ 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