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 someone help me with this request please?

Thanks in advance

You can use Conditional Formatting to accomplish your goal. Let's say your data blocks are in A1:F100.

Select the entire range and then choose Conditional Formatting from the Home ribbon.

Next choose New Rule and then "Use a formula to determine which cells to format."If there are no blanks cells within your data blocks, enter this formula, and then choose a fill color.

=OFFSET(A1,1,0)=""

Any cell that has an empty cell below it will be highlighted. Therefore, as long as there are no empty cells within the "body" of the data block, only the bottom row of cells in each block will be highlighted.

If there will be empty cells within the data block, then this should work:

=COUNTIF(OFFSET($A1:$F1,1,0),"")=6

Any row in columns A:F that has 6 empty cells below it will be highlighted.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History