Finding the next empty line

[Guest_imported]

Hey,

I use the following line to find the next empty line in column A. I have data in cells A5 and A18, so the following line finds the next empty line after A5 but before A18.

[A6:A65536].SpecialCells(xlCellTypeBlanks)(1, 1).Value = Format(Time(), "hh:mm&quot

Once I find the next empty line, I type information in the cells next to it, in this case Column B.

An example: Using the above code puts information in, say, Cell A7. I then type data in B7 but if the information doesn't fit in B7, I then type in B8. Now, if I run the code again it writes in A8 but I want it to recognize that something is in B8 and write in A9. Should nothing be in B8 then write to cell A8.

 

[Loomah]

Hi Chodo

try this.........

With [A6:A65536].SpecialCells(xlCellTypeBlanks)(1, 1)
If .Offset(0, 1) = "" Then
.Value = Format(Time(), "hh:mm&quot
Else: .Offset(1, 0).Value = Format(Time(), "hh:mm&quot
End If
End With

.......and let me know how you get on
Happy Friday!
;-)

 

[Guest_imported]

One step closer Loomah!

I tried your suggestion, but problems still occur. Example:
If we run the code and the time inserts in A6. I put data in B6. Run the code again and data goes in A7, which is fine. This time I write data which goes in B7 and B8, so the next time I run the code the time should go in B9 - which works.

But now if I do it again, nothing happens.

 

[Loomah]

OK,
Let's try this work around and see what happens. I've added a line to select the relevant cell (for watching puposes) and a line to ensure that cells that have no time in them are not left blank (the reason it wasn't working properly)

This isn't pretty but will get the show on the road.

Also, the code will not function as expected if you enter more than 2 rows of input in col B. Incidentally why are you using 2 cells for input in some cases?

With [A6:A65536].SpecialCells(xlCellTypeBlanks)(1, 1)
.Select 'added to watch
If .Offset(0, 1) = "" Then
.Value = Format(Time(), "hh:mm:ss&quot 'seconds added to watch
Else:
.Value = "*" 'lose blank cells to avoid reselection
.Offset(1, 0).Value = Format(Time(), "hh:mm:ss&quot'seconds added to watch
End If
End With

let me know how it goes
;-)

 

[Guest_imported]

I tried this and it worked fine. Hope you approve.

With [B6:B65536].SpecialCells(xlCellTypeBlanks)(1, 1)
If .Offset(0, -1) = "" Then
.Offset(0, -1).Value = Format(Time(), "hh:mm&quot
Else: .Offset(1, 1).Value = Format(Time(), "hh:mm&quot
End If
End With

 

[Loomah]

Chodo
If this works for you then it isn't for me to approve or otherwise, I'm just glad you have your solution!
However what happens if you don't enter any data?