How to count cells until first non-blank cell

[FoxIII]

So what I have is an unknown number of rows, starting at row 2. The first row (row 2) contains information from A2:M2 but all rows after this in column M will be blank up to a particular row. What I want to do, is count how many rows are blank in the column M up to the last blank cell and fill this with a specific word.

I have attached a basic layout workbook to show what I want.

There is a comment on cell M3

TIA

[Ron Coderre]

Using your posted example...
Try this:

• Select M2:M6
• From the Excel Main Menu:
...Edit.Go_To
...Click: Special Cells
...Check: Blank
...Click: OK

While only the blank cells are selected:
...Type: =
...Press the UP arrow
...Hold down CTRL and Press ENTER

Now each (formerly) blank cells displays that previous value from above.

To hardcode them:
• Select M2:M6
• CTRL+C ....a shortcut for Edit.Copy
• Edit.Paste_Special...Check: Values....Click: OK

Does that help?

[FoxIII]

Thanks Ron! That worked perfectly.

[bonviveur60]

Hi Ron

I found your answer to "counting to the first non-blank cell" posted on 15 Feb 2009 almost what I want. However my problem is that I need to count across each column in my table to find how many weekd (i.e. blank cells) since the last order, e.g in a range from H6..CZ6 to return to me the number of blanks before the first cell with data (i.e. last order).

I thought I would be clever by substituting RIGHT arrow in your formula but without success.

Can you help please

[arlu1201]

bonviveur60,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.