Finding the next empty line

BenNV

Board Regular
Joined
Mar 27, 2002
Messages
160
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi. Please Try this too.

<pre>
Sub Test()
Workbooks("fx.xls").Worksheets("Report") _
.[A5].End(xlDown).Offset(1).Formula = "=now()"
End Sub
</pre>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top