Find first blank row across 2 columns

[gers1978]

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.ozgrid.com/forum/showthread.php?t=200504&p=774147#post774147

 

[CsJHUN]

Hi gers

there is any other information below first blank cell in A or B, there is any random blank cell in your columns, or there is no data after first empty cell?

 

[gers1978]

Hi gers

there is any other information below first blank cell in A or B, there is any random blank cell in your columns, or there is no data after first empty cell?

Yes, there could be a number below the first blank cell in each.

For example A1:A8 might contain:

1
(blank)
2
3
(blank)
4
(blank)

And B1:B8 might contain:

(blank)
1
(blank)
2
3
(blank)
(blank)
4

 

[CsJHUN]

Hi

Try this, but as always suggested make a backup before doing it, and please check the 2 red comment

Sub gers1()
lra = ActiveSheet.Range("a" & Rows.Count).End(xlUp).Row
lrb = ActiveSheet.Range("b" & Rows.Count).End(xlUp).Row
If lra < lrb Then
lr = lrb
Else
lr = lra
End If
For i = 1 To lr
Range("A" & i).Select
If ActiveCell.Offset(0, 1).Value = "" And ActiveCell.Value = "" Then
ActiveCell.Value = "thizempty" [COLOR=#ff8c00]'''your formula to column A[/COLOR]
ActiveCell.Offset(0, 1) = "thizemptytoo" [COLOR=#ff8c00]'''your formula to column B[/COLOR]
End If
Next
End Sub

 

[Peter_SSs]

You could also try this non-looping method in a copy of your workbook.

Sub FirstBlankPair()
  Dim fb As Long
  
  With Columns("A").Resize(ActiveSheet.UsedRange.Rows.Count + 1)
    fb = Application.Min(Evaluate(Replace(Replace("if(len(#)=0,if(len(^)=0,row(#),""""),"""")", "#", .Address), "^", .Offset(, 1).Address)))
  End With
  Range("A" & fb).Resize(, 2).Interior.Color = vbRed
End Sub

 

[gers1978]

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.

File is here:

https://dl.dropboxusercontent.com/u/2213114/Test.xlsm

 

[gers1978]

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

 

[Peter_SSs]

In your sample, the first row with both columns blank appears to be row 25, which is the row below all your data. Will that always be the case or could the first time a pair of blank rows appear be in among your existing data? For example, could cell B17 be empty, and if so, should row 17 be used for the new EWN or CEN?

 

[gers1978]

In your sample, the first row with both columns blank appears to be row 25, which is the row below all your data. Will that always be the case or could the first time a pair of blank rows appear be in among your existing data? For example, could cell B17 be empty, and if so, should row 17 be used for the new EWN or CEN?

It'll always be at least 25 (using the attachment as an example). In other words every used row will have either an EWN # (col A) or CEN # (col B). Any row that has A & B blank should be the next totally blank row

 

[Peter_SSs]

It'll always be at least 25 (using the attachment as an example). In other words every used row will have either an EWN # (col A) or CEN # (col B). Any row that has A & B blank should be the next totally blank row

In that case here are some alternative codes.

Sub GetNextEWN()
  With Range("A" & Columns("A:B").Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
                                        SearchDirection:=xlPrevious, SearchFormat:=False).Row + 1)
    .FormulaR1C1 = "=MAX(R12C:R[-1]C)+1"
    .Value = .Value
  End With
End Sub


Sub GetNextCEN()
  With Range("B" & Columns("A:B").Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
                                        SearchDirection:=xlPrevious, SearchFormat:=False).Row + 1)
    .FormulaR1C1 = "=MAX(R12C:R[-1]C)+1"
    .Value = .Value
  End With
End Sub