I have an excel data sheet which is divided into data blocks of variable number of rows.

Data blocks are separated from each other with an empty row.

What I am in a dire need for is a macro that highlights the last row of each data block; which is the row above the empty row.

Can I get some help with this request please? Thanks in advance


You'll want to use Conditional Formatting. Put the cursor in the first cell of the column (I'm going to assume its A1 for this example). Create a Conditional Formatting rule with "Use a formula to determine which cells to format" with the following formula: =IF(ISBLANK(OFFSET(A1,1,0)),TRUE,FALSE)

enter image description here

Once the rule is created, right click, select Format Painter, and drag it down the column to apply that format to all relevant cells. If you apply the formatting rule to the entire column, the "Applies to" field should read =$A:$A

Format Painter

enter image description here

  • Thank you P Fitz for your answer. I have pasted the formula in a new rule, but it did not actually create any change; Any suggestions? – Josef Miller Dec 17 '14 at 21:10
  • Did you set a particular format for the affected cells? Also, double check the "applies to" field. – P Fitz Dec 17 '14 at 21:23
  • Yes, I set a particular format, and applied it to the entire sheet. I tested it on different sheets, and actually got the same result, no change has applied. I believe it is me missing something. – Josef Miller Dec 17 '14 at 21:52
  • Now I know where my mistake was. I'm so grateful P Fitz. Thank you very much as it works as exactly as desired. All the best. – Josef Miller Dec 18 '14 at 5:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.