Finding blank cell in row whilst avoiding blank cells if non-blank cells are to the right of it

[Milos]

Hi there,

Sorry for the massive title!

I am using a helper column that works well, but I want it to smarten my find code up to avoid finding the first empty cell in row 1 as often the columns with an empty header still contain data but someone has often been messy when designing a spreadsheet and left a header blank. So I want the find code to avoid blank cells if there is non-blank cells to the right of it. Does anyone know how I would re-wire my code to achieve this?

Set helper_column = range("1:1").Find("", , , xlWhole, , , False, , False)

So in my terrible example below I want my find code to avoid Column C as there is data to the right of the cell

	A	B	C	D	E	F
1	Sunny	Rainy		Cloudy	Stormy	This is the cell i want to reference
2	5 days	1 day	days present	3 days	0 days

<tbody>
</tbody>

Cheers,
Milos

 

[Rick Rothstein]

What if both C1 and D1 were blank... what cell would you want referenced?

Are you simply looking for the blank cell after the last data cell in Row 1?

 

[Milos]

If they are both blank then F1 is the cell that I would want referenced. So yes the blank cell after the last data cell in row 1.

 

[Rick Rothstein]

If they are both blank then F1 is the cell that I would want referenced. So yes the blank cell after the last data cell in row 1.

Try this...

Set Helper_Column = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

 

[Milos]

Perfect just what I was after. Thanks Rick!