Excel 2007 Pasting Conditional Formats causes 'Selection too large' error
Hi Experts,
I have an Excel 2007 xlsm file that's about 12 MB, 25,000 rows and 28 columns, 25 of which comprise the dataset. I am trying to find a way to copy the Conditional Formats from Row 9 to the other 25,000 rows and having 2 problems.
1st, I can't seem to make the copy relative. If I select the range A9:Y9 and specify Conditional Formatting formula criteria like:
="$Y9=""RUNWAY ACTIVE""" (and Format the Fill color as Blue)
= "$Y9=""RUNWAY NOT ACTIVE""" (and Format the Fill color as Red)
And then with the range A9:Y9 selected, use the Format Painter to paste the formats to (in the uploaded CFTest file which has only 30 records) the range B9:Y30, I then go to cell Y18 for example, and look at the CF rules, it is still referencing Row 9,in Row 18 like this:
="$Y9=""RUNWAY NOT ACTIVE"""
... even though I did not use an absolute row reference.
How do I make the Pasting of Conditional Format formula criteria relative?
2nd, When I try to use either the Format Painter as above or Copy, Paste Special | Formats in the original 25,000 row file, I get the error "Selection too large". And if I try to use code like:
Sub PasteFormat()
[A9:Y9].Copy
[A10:Y25674].PasteSpecial Paste:=xlPasteFormats
End Sub
... I get a "Run-time error 1004: Selection is too large".
How can I work around this issue?
Thanks for any insights!
Jeff
CFTest.xlsm
I have an Excel 2007 xlsm file that's about 12 MB, 25,000 rows and 28 columns, 25 of which comprise the dataset. I am trying to find a way to copy the Conditional Formats from Row 9 to the other 25,000 rows and having 2 problems.
1st, I can't seem to make the copy relative. If I select the range A9:Y9 and specify Conditional Formatting formula criteria like:
="$Y9=""RUNWAY ACTIVE""" (and Format the Fill color as Blue)
= "$Y9=""RUNWAY NOT ACTIVE""" (and Format the Fill color as Red)
And then with the range A9:Y9 selected, use the Format Painter to paste the formats to (in the uploaded CFTest file which has only 30 records) the range B9:Y30, I then go to cell Y18 for example, and look at the CF rules, it is still referencing Row 9,in Row 18 like this:
="$Y9=""RUNWAY NOT ACTIVE"""
... even though I did not use an absolute row reference.
How do I make the Pasting of Conditional Format formula criteria relative?
2nd, When I try to use either the Format Painter as above or Copy, Paste Special | Formats in the original 25,000 row file, I get the error "Selection too large". And if I try to use code like:
Sub PasteFormat()
[A9:Y9].Copy
[A10:Y25674].PasteSpecial Paste:=xlPasteFormats
End Sub
... I get a "Run-time error 1004: Selection is too large".
How can I work around this issue?
Thanks for any insights!
Jeff
CFTest.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi all & thanks for posting.
@ J79123:
> 1. I don't seem to be getting that error, so it may be relative to your pc setup.
The error only occurs with the original 12 MB 25k record file, not the 30 record example I uploaded.
> 2. Regarding relative referencing ...
If you're not getting the result you thought you would, it's probably due to the order that the rules are being applied, or the stop if true option.
In the uploaded CFTest file, I went through the review you suggested in 3. above, made some changes, and I'm still not getting the results I expected. I have uploaded screen shots of both the CF Dialog and the resulting application of the CF's. As can be seen on Rows 18 and 25, the CF's did not display.
>3. For applying it to 25k rows: Have you tried
-going into the edit criteria formula window,
-Change the dropdown from current selection to "This worksheet" (looking at the formats for the entire worksheet is good, so you can make sure there aren't any conflicting rules going on)
-Then, for each of the rules, change the 'applies to' range accordingly?
I can't get it to work with the uploaded file yet ... if I can get past that, I hope to be able to apply it to the original 25K file ...
@ teylyn
I had come across that Contextures blog but couldn't figure out how to apply the code to my situation ... though I am willing to learn ;--).
Jeff
@ J79123:
> 1. I don't seem to be getting that error, so it may be relative to your pc setup.
The error only occurs with the original 12 MB 25k record file, not the 30 record example I uploaded.
> 2. Regarding relative referencing ...
If you're not getting the result you thought you would, it's probably due to the order that the rules are being applied, or the stop if true option.
In the uploaded CFTest file, I went through the review you suggested in 3. above, made some changes, and I'm still not getting the results I expected. I have uploaded screen shots of both the CF Dialog and the resulting application of the CF's. As can be seen on Rows 18 and 25, the CF's did not display.
>3. For applying it to 25k rows: Have you tried
-going into the edit criteria formula window,
-Change the dropdown from current selection to "This worksheet" (looking at the formats for the entire worksheet is good, so you can make sure there aren't any conflicting rules going on)
-Then, for each of the rules, change the 'applies to' range accordingly?
I can't get it to work with the uploaded file yet ... if I can get past that, I hope to be able to apply it to the original 25K file ...
@ teylyn
I had come across that Contextures blog but couldn't figure out how to apply the code to my situation ... though I am willing to learn ;--).
Jeff
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry it has taken me so long to get back to this - this is primarily due to ongoing computer issues I'm having, including the inability to use an Advanced Filter on this file (actually, a "parent" file of this same 12 MB file. While both my problems (the CF issue here and this other Advanced Filter issue which I also opened a separate EE question for) have now "gone away", I don't really know what the problems (or the solutions) were ... can only surmise some kind of corruption in the file that resolved when I rebuilt the file. Under the circumstances, my selection of a "solution" will be largely arbitrary, but I do want to award points to reflect my appreciation for your help. I'm awarding most of the points to J79123, because his/her comment: "On the edit area of conditional format, it's always going to show the formula in terms of the first cell in the range that that rule "applies to" (in your example it's y9) but on the subsequent rows, it really is looking at rows 10,11 etc. to evaluate the formula" served to help me make sense of what the resulting (subsequent) CF's should look like.
@ imnorie: I think my problems lay elsewhere (And re-building the file is seemingly what it took to fix it), but I appreciated your code solution, too, and would have turned to that if the re-build hadn't helped.
Thanks to all.
Jeff
@ imnorie: I think my problems lay elsewhere (And re-building the file is seemingly what it took to fix it), but I appreciated your code solution, too, and would have turned to that if the re-build hadn't helped.
Thanks to all.
Jeff
Interestingly enough, I just read one of the latest blog posts of the contextures.com blog, which covers exactly that issue. Maybe you can use it.
http://blog.contextures.com/archives/2010/10/18/excel-error-selection-is-too-large/
cheers, teylyn