+ Reply to Thread
Results 1 to 8 of 8

Select Alternating Columns With Unknown End Point

  1. #1
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Select Alternating Columns With Unknown End Point

    Okay, so I have been googling to figure out how to write a macro that will select every other column on my workbook. All that keeps coming up is conditional formatting, which I do not want. I am not trying to highlight the columns in alternating colors, I just want to select them.

    I am starting at column F and I would like it to search until the end of the columns and select every other column ending with the last column with data in it.

    I know I could search by a specific row and tell it to find the first blank cell then offset it, but there is no particular row that will always have data in it, so I guess it needs to check by column until it finds the first column without any data in all rows and then offset it.

    Anyone know what I am talking about here?

    I know it should be fairly simple but I couldn't seem to find any answers anywhere else.

    Also, anyone know any good websites that have definitions for some basic macros? I'd love to start learning how to write this stuff on my own.

    Thanks.

    -Amy Kate
    Last edited by amyxkatexx; 03-09-2010 at 04:50 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Select Alternating Columns With Unknown End Point

    Hello Amy Kate,

    This macro will start with column "F" (6) and select alternating columns up to the Last Column with a heading in row 1. The end of each column is found automatically and the columns are assembled into a single range and then selected.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,965

    Re: Select Alternating Columns With Unknown End Point

    Try

    Please Login or Register  to view this content.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  4. #4
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Select Alternating Columns With Unknown End Point

    This macro will start with column "F" (6) and select alternating columns up to the Last Column with a heading in row 1.
    That won't work because I do not have headers in row one. One of the things I pointed out in my original post is that there are no rows that will always have data in them, so I need it to search by column for any data in any row in that column.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Select Alternating Columns With Unknown End Point

    Hello Amy Kate,

    Since you are not following standard conventions in your layout, it would help if you post your workbook. This will clear up any ambiguities or misunderstandings about what you want. If possible you should include a before and after example of the data.

  6. #6
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Select Alternating Columns With Unknown End Point

    I was unable to attach my current spreadsheet because it is too large, but I made a simple workbook with 2 sheets, labeled "Before" and "After".

    You will see that the only difference between the two is that the second sheet has a few columns selected. I do not need to do any more than simple select them (activate them), because once they are selected I will be able to do what I need to do.

    This sample only has information to column L, but the info could in fact go 100's of columns further than column L, so that is why I need to be able to find the last column. But as you can see, none of my rows have information in every cell from A to L, so I need some other way of searching for the last column other than by a row.

    That is why I suggested that there might be some sort of macro that can search based on an entire column, and if any data exists in any row of the column, then that would be my last column. (If searching from right to left.) I just can't find this anywhere.

    Hope that explains my problem more clearly.

    Thanks!
    Attached Files Attached Files

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Select Alternating Columns With Unknown End Point

    Hello Amy Kate,

    Thanks for posting the workbook. I revised the macro and attached it to button on the "After" sheet. When clicked it will select the columns from "F" to the last column with data. Here is the macro which has been added to the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Contributor amyxkatexx's Avatar
    Join Date
    11-23-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    128

    Re: Select Alternating Columns With Unknown End Point

    That works perfectly!

    I can't thank you enough!

    :]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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