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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD] Sunny[/TD]
[TD]Rainy[/TD]
[TD][/TD]
[TD]Cloudy[/TD]
[TD]Stormy[/TD]
[TD]This is the cell
i want to reference[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]5 days[/TD]
[TD]1 day[/TD]
[TD]days present[/TD]
[TD]3 days[/TD]
[TD]0 days[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!