Find First Blank in Non-Contiguous Range - VBA

[ExcelKnut]

I 'm trying to find the first blank cell in a non-contiguous range (see code below). When all five cells are blank, "C4" is assigned to variable x. "C2" is the expected and correct value. Any idea why that's happening? No doubt I'm doing something wrong.

Set x = Worksheets("load_report").Range("C2,C4,C6,C8,C10").Find(what:="", lookat:=xlWhole)

 

[Rick Rothstein]

I 'm trying to find the first blank cell in a non-contiguous range (see code below). When all five cells are blank, "C4" is assigned to variable x. "C2" is the expected and correct value. Any idea why that's happening? No doubt I'm doing something wrong.

Set x = Worksheets("load_report").Range("C2,C4,C6,C8,C10").Find(what:="", lookat:=xlWhole)

I think it is because the Find function starts looking after the first cell in the range (when a starting cell is not specified), not at that cell. Try changing your code line to this and see if it works better for you...

Set x = Worksheets("load_report").Range("C2,C4,C6,C8,C10").Find(What:="", After:=Range("C10"), LookAt:=xlWhole)

 

[ExcelKnut]

Rick,

Thanks for taking the time to provide this information. I greatly appreciate it. I'll give that a try.

Jeff