In attached example, range A1:I12 is grayed out because A1:A2 is blank.
I used =ISBLANK($A$1:$A$2) and applied to A1:I12 range.
I tried to make a "blanket rule" for all the rows in entire A1:I12, and failed, of course
Appreciate your time ...
Excel 2007 file
Last edited by geopiet; 12-24-2012 at 09:13 PM. Reason: [SOLVED]
Use this rule instead
Please Login or Register to view this content.
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks for quick turnaround
Yes, it works great in the example provided, but when I incorporated it into the real file, it stumbles a bit, mixing up the rows
I modified the example file to resemble the actual one.
Can you update your sample to and after? Not quite sure I see what you are asking.
HTH
Regards, Jeff
Hi Jeff,
I updated the original sample ( "cf-blank row.xlsx" from my first post) with the code supplied by Ace_XL, and it worked just fine.
However when I tried to use it in the actual file, it doesn't work properly. It skips some rows, and/or grays out the ones it not suppose to.
Yo can see it in the second attachment, named " cf-blank row-Ace_XL.xlsx" , or in this screenshot -> http://goo.gl/6D7p3
Thank you for your time
Formula:
Please Login or Register to view this content.
Those merged cells in A make things difficult. The formula only looks at the odd A's for each pair of rows because Excel only uses the address of the top left cell of any merged group. I.e. A1:A2 is referenced with just A1, etc.
Last edited by protonLeah; 12-23-2012 at 09:25 PM.
Ben Van Johnson
Ben, i have been scratching my head on this 1 too. I was using the file from post #3 tho
was also playing aroudn with mod(), but couldnt think how to get it to work, good 1
Last edited by FDibbins; 12-23-2012 at 09:25 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
in that case this should also work
=ISBLANK(INDEX(A:A,IF(MOD(ROW(),2),ROW(),ROW()-1)))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thank you guys for taking your time with this, especially at this time of the year.
I got excited about prontoLeah's solution. it works just as intended.
The one from martindwilson has some kinks in it, it misbehaves
Unfortunately, when plugged into the actual file, prontoLeah's code grays out everything, regardless if the cells are blank or not.
See screenshot -> http://goo.gl/lZGli
And here is screenshot of the same file, but with martindwilson's formula -> http://goo.gl/AbtyE
Attached file "cf-blank row - prontoLeah" contains prontoLeah's formatting.
Again, thank you everyone, and happy holidays and New Year.
you start in an even row so the argument needs to be reversed
=ISBLANK(INDIRECT("c" & IF(MOD(ROW($C6),2),ROW($C6)-1,ROW($C6))))
or
=ISBLANK(INDEX($C:$C,IF(MOD(ROW($C6),2),ROW($C6)-1,ROW($C6))))
edit mind you ,you can just use row()
=ISBLANK(INDIRECT("c" & IF(MOD(ROW(),2),ROW()-1,ROW()))) =ISBLANK(INDEX($C:$C,IF(MOD(ROW(),2),ROW()-1,ROW())))
Last edited by martindwilson; 12-24-2012 at 07:24 AM.
Thank you martindwilson and others,
I used the first formula from your post. It works.
Didn't try the others yet.
Happy holidays and New Year
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks