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

**ZooTV92** · 10-12-2015, 10:04 AM

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!

**TMS** · 10-12-2015, 10:25 AM

Please Login or Register  to view this content.

Regards, TMS

**ZooTV92** · 10-12-2015, 11:00 AM

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.

**Greg M** · 10-12-2015, 11:35 AM

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

**TMS** · 10-12-2015, 11:44 AM

This works for me (only one minor change):

Please Login or Register  to view this content.

Regards, TMS

**ZooTV92** · 10-12-2015, 02:30 PM

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.

**TMS** · 10-12-2015, 03:50 PM

Please Login or Register  to view this content.

Regards, TMS

**ZooTV92** · 10-12-2015, 05:25 PM

Awesome. That worked perfectly.

Thanks again for your time!

**TMS** · 10-12-2015, 06:56 PM

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

**Greg M** · 10-12-2015, 07:38 PM

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

**Greg M** · 10-12-2015, 07:47 PM

In which case my original solution should have been:

Please Login or Register  to view this content.

Best regards,

Greg M

**TMS** · 10-12-2015, 07:54 PM

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

**Greg M** · 10-13-2015, 02:55 AM

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

**TMS** · 10-13-2015, 04:17 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Trying to copy fixed row of cells, paste to next empty line, and clear fixed line.

Delete fixed Rows and Columns

Copying a formula with fixed rows but different columns

Lookup from fixed rows and dynamic columns

Macro to select fixed amount of cells to the right and left

Dynamic Range of Columns with fixed Rows

[SOLVED] Excel 2000 - Fixed size columns and rows?

Bookmarks

Bookmarks

Posting Permissions