# Find first blank row across 2 columns

• Re: Find first blank row across 2 columns

Hi..

I think this does what you want..

Code
Private Sub CommandButton1_Click()
Dim x, i As Long
x = Range("A1:B" & Range("A" & UsedRange.Rows.Count).Row)
For i = LBound(x) To UBound(x)
If IsEmpty(x(i, 1)) = True And IsEmpty(x(i, 2)) = True Then
Cells(i, 1).Value = "=1"
Exit Sub
End If
Next i
End Sub
• Re: Find first blank row across 2 columns

Ok, thanks, but I think an example of what I'm trying to do would be better. Attached is a sample sheet with dummy data.

What I'm trying to achieve is a button to add a new EWN. When clicked it should:

1) Look down column A (starting at A12) for the first blank row
2) Make sure that column B of the same row is also blank
3) Work out the maximum number in any of the cells above in column A (so in my attached example this would be 6)
4) Populate the cell in A with this value

I also want a button to add a new CEN, which does the same as above but for column B, i.e. find the first blank row in B, but also make sure the equivalent cell in A is blank, before taking the next sequential number from column B's values.

There's a slight complication in column B in that not all values so far are numerical, but I'll cross that bridge when I come to it.

forum.ozgrid.com/index.php?attachment/69721/

## Files

• Re: Find first blank row across 2 columns

Hi..

Pretty much the same.. just some minor modifications..

Code
rivate Sub CommandButton1_Click()
Dim x, i As Long
x = Range("A12:B" & Range("A" & UsedRange.Rows.Count).Row)
For i = LBound(x) To UBound(x)
If IsEmpty(x(i, 1)) = True And IsEmpty(x(i, 2)) = True Then
Cells(i + 11, 1).Value = Evaluate("=Max(A12:A" & Range("A" & Rows.Count).Row & ")") + 1
Exit Sub
End If
Next i
End Sub
• Re: Find first blank row across 2 columns

I came up with this myself, it SEEMS to work but I'm not sure if it's flawed:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!