Thanks:  0
Likes:  0

# Thread: Finding the next empty line

1. Hello,

I want to start at row 5 and find the next empty line. Once it has found the line I want to add the formula =NOW() to the column A for the first empty row.

Thank you.

2. To find the next blank cell in column B, place the following eqn in col A:

=IF(COUNTBLANK(\$B\$5:\$B5)=1,NOW(),"")

To label every blank row:
=IF(COUNTBLANK(B5:IV5)=255,NOW(),"")

To label the first blank row, if cell A4 is blank:
=IF(AND(COUNT(\$A\$4:\$A4)=0,COUNTBLANK(B5:IV5)=255),NOW(),"")

To label the first blank row, if cell A4 contains a number:
=IF(AND(COUNT(\$A\$4:\$A4)=1,COUNTBLANK(B5:IV5)=255),NOW(),"")

You can also investigate array formulas to do this type of thing.

Regards,

Brian.

3. Thanks, Brian.

How would I do this in VBA?

Sub insert_info()
Dim i As Integer

Set FixWS = Workbooks("fx.xls").Worksheets("Report")

rr = 5

todaysDate = CLng(Int(Now()))

If (FixWS.Cells(rr, 1).Value = "") Then
rr = rr + 1
Else
FixWS.Cells(rr, 1).Select
End If

End Sub

4. Sorry ignore last message. I want to try and do this in VBA. This is what I have:

Sub insert_flow()

Dim i As Integer

Set FixWS = Workbooks("fx.xls").Worksheets("Report")

rr = 5

If (FixWS.Cells(rr, 1).Value = "") Then
rr = rr + 1
Else
FixWS.Cells(rr, 1).Formula = "=NOW()"
End If

End Sub

This doesn't seem to work.

5.

6. Anyone know how to do this in VBA?

7. Try this :-

Sub insert_flow()
Dim rr#
ActiveSheet.UsedRange
rr = Cells.SpecialCells(xlCellTypeLastCell)).Row+1
Cells(rr, 1).Formula = "=NOW()"
End Sub

[ This Message was edited by: dwhj on 2002-05-08 00:34 ]

8. Thanks for the reply. I should have explained better. I already have some data there, i.e. in cells A5, A25, A45, A65.

I want the code to insert the formula after A5. At the moment it goes to A66.

9. Hi. Please Try this too.

```
Sub Test()
Workbooks("fx.xls").Worksheets("Report") _
.[A5].End(xlDown).Offset(1).Formula = "=now()"
End Sub
```

10. Or if you need to check all columns in each row instead of just column A :-

Sub insert_flow()
Dim rng As Range, cell As Range
Set rng = Intersect([A4:A65536], ActiveSheet.UsedRange).Offset(1, 0)
For Each cell In rng
If Application.WorksheetFunction.CountA(Range(cell, cell(1, 256))) = 0 Then
cell.Formula = "=NOW()"
Exit For
End If
Next
End Sub

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•