Formula for first blank cell in a range

[ccordner]

I am producing a roster and need to find out if a person has a day off during a week.

At the moment, I have a very complicated formula saying "if(g3="",...,if(h3="",.. etc.

Is there any formula I can use to find the first blank cell in a range (1 row x 7 columns)? Specifically, I need it to return the column number (either within the range, or absolute).

Thanks
Chris

 

[ccordner]

I was given this to solve a similar problem with columns:

Set wksSource = Worksheets("Sheet Name")
    With wksSource
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    End With

Would this be any use?

 

[Kdilas]

I believe that code only searches a single column for the first blank, starting from the bottom. I need to search a range of cells starting from the beginning. Range would be B10:H100.

 

[ccordner]

I'm not sure to be honest. I use it to find the first blank cell in a column, so I think it starts from the top but I' don't know. I'm sure somebody on here will though!

 

[ccordner]

If it's the last Blank Cell in a range, how about something like this?

It's a bit clunky, but that searches bottom to top, right to left. To do rows first, just swap the row/column functions around.

At the end, RowNumber/ColNumber give you the last empty cell?

Sub Example()
Dim Found, RowNumber, ColNumber
Found = False
Proof = 1
Do While Found = False
    
    ColNumber = 8
    
    For a = 1 To 7
    
        RowNumber = 100
    
        For b = 1 To 90
    
            If Sheets("Sheet1").Cells(RowNumber, ColNumber) = "" Then
                Found = True
            End If
            
            RowNumber = RowNumber - 1
    
        Next
    
        ColNumber = ColNumber - 1
    
    Next
        
Loop
End Sub