+ Reply to Thread
Results 1 to 14 of 14

Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

  1. #1
    Forum Contributor
    Join Date
    01-30-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    102

    Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    I have a fixed range of cells, F6:L59. I need to be able to select all cells in the even rows only (6 to 58), but only from columns F to L.

    I would like to make a cleaner macro other than F6...select, G6....select, H6....select, etc.

    Once all cells are selected, I would then run clearcontents. I can't select more than one cell at a time, otherwise i get an error message due to another piece of code that runs down the columns of the same range.

    Thank you!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    01-30-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    102

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    This works, in terms of the range of cells are correct, but unless every cell is selected individually, I will get the error. Is there any way to do that?

    Thank you.

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Hi there,

    Try the following code and see if it does what you need:

    Please Login or Register  to view this content.
    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    This works for me (only one minor change):

    Please Login or Register  to view this content.

    Regards, TMS

  6. #6
    Forum Contributor
    Join Date
    01-30-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    102

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Thanks for the replies guys. Unfortunately I am still getting the error traced back to the code that runs down the affected columns. Now that I see how the code you gave me frames the range, I will work on trying to come up with code that selects each cell individually. It's the only way to delete the contents of the cells without the error popping up.

    Thank you for your time.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Please Login or Register  to view this content.

    Regards, TMS

  8. #8
    Forum Contributor
    Join Date
    01-30-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    102

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Awesome. That worked perfectly.

    Thanks again for your time!

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    You're welcome. Thanks for the rep.


    Just occurred to me that the problem you experience may be because you have a Worksheet Change event handler that is only designed/intended to process single Target cells. If that is the case, the workaround provided will fire the WSCeh dozens of times for no reason.

    You should maybe try this:

    Please Login or Register  to view this content.
    That should make the code a lot faster. No loop and no event handling for each cell change.


    Regards, TMS

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Hi TMS,

    I was scratching my head trying to figure out why a ClearContents command applied to the entire range was producing an error condition, but you've probably hit the nail on the head!

    May I be so bold as to suggest a slight improvement in your latest code? If (for whatever reason) an error condition arises when the "rSelect.ClearContents" command is executed, Events will remain disabled and the User will be totally unaware of this.

    The following amendment re-enables Events in such a situation:

    Please Login or Register  to view this content.
    Best regards,

    Greg M

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    In which case my original solution should have been:

    Please Login or Register  to view this content.
    Best regards,

    Greg M

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Happy for you to improve the code. But I think it may be a little over-engineered

    Given that the .ClearContents is unlikely to cause a problem itself, other than if a single target cell event handler is in place, and that scenario is covered now, I'm not sure what would cause the failure you have in mind. And, given there is only the one line of code between switching event handling off and on again, you can perhaps just get away with On Error Resume Next.

    You could probably test both approaches by protecting the worksheet.


    Regards, TMS

  13. #13
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Hi TMS,


    Given that the .ClearContents is unlikely to cause a problem itself . . .


    Eh? The .ClearContents is exactly what DID cause the problem in the first place!

    Anyway, let's agree to disagree then - happy to exchange views with you.

    Best regards,

    Greg M

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select Cells Individually, Fixed Columns, Fixed Rows, Every Other Row

    Yes, we'll have to agree to disagree. I think the worksheet change event handler is only able to process a single cell and it is being passed a range of discontinuous cells. Which is why the loop to clear single cells doesn't error.

+ 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. Trying to copy fixed row of cells, paste to next empty line, and clear fixed line.
    By oh_stanley in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-24-2014, 07:44 PM
  2. Delete fixed Rows and Columns
    By brumar in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-07-2014, 02:36 PM
  3. Copying a formula with fixed rows but different columns
    By Mikethebike19 in forum Excel General
    Replies: 3
    Last Post: 11-27-2013, 06:34 AM
  4. Lookup from fixed rows and dynamic columns
    By Alexvs in forum Excel General
    Replies: 2
    Last Post: 11-09-2010, 05:39 AM
  5. Replies: 3
    Last Post: 08-21-2009, 06:02 AM
  6. Dynamic Range of Columns with fixed Rows
    By nms2130 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-05-2009, 05:24 PM
  7. Excel 2000 - Fixed size columns and rows?
    By stainless in forum Excel General
    Replies: 2
    Last Post: 07-29-2005, 06:05 PM

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