Find first blank row across 2 columns

gers1978 · Jul 19th 2016

I want to be able to find the first blank row in column A and put a formula in there, but ONLY if column B is also blank.

So effectively I think I'm looking to find the first row where both columns A & B are blank.

Thanks

Also posted here: http://www.mrexcel.com/forum/e…-columns.html#post4581074

apo · Jul 19th 2016

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

gers1978 · Jul 19th 2016

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/

apo · Jul 19th 2016

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

gers1978 · Jul 19th 2016

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:

Code

Sub GetNextEWN()
    Dim lastRowA As Integer
    Dim lastRowB As Integer
    
    lastRowA = ActiveSheet.Cells(1048576, 1).End(xlUp).Row + 1
    lastRowB = ActiveSheet.Cells(1048576, 2).End(xlUp).Row + 1
    
    If lastRowA >= lastRowB Then
        ActiveSheet.Range("A" & lastRowA).Value = WorksheetFunction.Max(Range("A12:A" & lastRowA)) + 1
    Else
        ActiveSheet.Range("A" & lastRowB).Value = WorksheetFunction.Max(Range("A12:A" & lastRowA)) + 1
    End If
    
End Sub
Sub GetNextCEN()
    Dim lastRowA As Integer
    Dim lastRowB As Integer
    
    lastRowA = ActiveSheet.Cells(1048576, 1).End(xlUp).Row + 1
    lastRowB = ActiveSheet.Cells(1048576, 2).End(xlUp).Row + 1
    
    If lastRowB >= lastRowA Then
        ActiveSheet.Range("B" & lastRowB).Value = WorksheetFunction.Max(Range("B12:B" & lastRowB)) + 1
    Else
        ActiveSheet.Range("B" & lastRowA).Value = WorksheetFunction.Max(Range("B12:B" & lastRowB)) + 1
    End If
    
End Sub

Display More

Find first blank row across 2 columns

Participate now!

Share