To get to a intermittent last cell of a column

[tvsramesha]

[TABLE="width: 599"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]HI Everybody -

Pl guide me to find intermittent last empty cell of a column, in column B need to find last intermittent cells ex:B234, B445,B561 ......

Below is the example of data.

Thanks in advance,

Best,

Ramesha

[TABLE="width: 599"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sensor-óSensor-32[/TD]
[TD="align: right"]49.81[/TD]
[/TR]
[TR]
[TD]Sensor-29[/TD]
[TD="align: right"]50.05[/TD]
[/TR]
[TR]
[TD]Sensor-19Sensor-20[/TD]
[TD="align: right"]50.28[/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-29[/TD]
[TD="align: right"]50.54[/TD]
[/TR]
[TR]
[TD]Sensor-24Sensor-29[/TD]
[TD="align: right"]50.78[/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-32[/TD]
[TD="align: right"]51.01[/TD]
[/TR]
[TR]
[TD]Sensor-29Sensor-29[/TD]
[TD="align: right"]51.27[/TD]
[/TR]
[TR]
[TD]Sensor-20Sensor-26[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD]Sensor-20Sensor-23[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD]Sensor-24Sensor-29[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD]Sensor-32Sensor-29[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-26[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-18[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD]Sensor-17Sensor-23[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-18[/TD]
[TD="align: right"]51.53[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NEW-ROLL[/TD]
[TD]NEW-ROLL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Try this:

Sub My_First_Empty_Cell()
'Modified  8/12/2018  12:56:00 AM  EDT
Dim FirstEmptycell As Long
FirstEmptycell = Worksheets("Sheet1").Range("B1").End(xlDown).Row + 1
MsgBox FirstEmptycell
End Sub

 

[Macropod]

Cross-posted at: http://www.vbaexpress.com/forum/showthread.php?63407-Jumping-to-intermittent-last-cells-in-a-column
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[tvsramesha]

Thanks, it helped in beginning, now if I, need to continue looking for intermittent last cell further down from first empty cell from your code, how I, extend the loop?

 

[My Aswer Is This]

So where do you want the results entered.
And how many times do we keep doing this the column has 1.5 million rows

What are you trying to do as a end result?

 

[tvsramesha]

So where do you want the results entered.
And how many times do we keep doing this the column has 1.5 million rows

What are you trying to do as a end result?

_ Basically I'm logging data from sensors, in column B whenever one set of data ends, then again fresh set of data is logged onto the same column after few cells in that column and this repeats for about 200 sets in a day, I, need to bring the last cell value of each set of data, in the following example 51.33 is the last cell where the data is logged and next a new roll starts - new set of data

[TABLE="width: 679"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sensor-25Sensor-29[/TD]
[TD="align: right"]48.48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-30Sensor-25[/TD]
[TD="align: right"]48.72[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-17Sensor-07[/TD]
[TD="align: right"]48.98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-32Sensor-12[/TD]
[TD="align: right"]49.24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-06Sensor-06[/TD]
[TD="align: right"]50.07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-óSensor-32[/TD]
[TD="align: right"]49.81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-29[/TD]
[TD="align: right"]50.05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-19Sensor-20[/TD]
[TD="align: right"]50.28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-29[/TD]
[TD="align: right"]50.54[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-24Sensor-29[/TD]
[TD="align: right"]50.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-32[/TD]
[TD="align: right"]51.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-29Sensor-29[/TD]
[TD="align: right"]51.27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-20Sensor-26[/TD]
[TD="align: right"]51.53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-20Sensor-23[/TD]
[TD="align: right"]51.53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-24Sensor-29[/TD]
[TD="align: right"]51.53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-32Sensor-29[/TD]
[TD="align: right"]51.53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-26[/TD]
[TD="align: right"]51.53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-18[/TD]
[TD="align: left"]

<tbody>
[TD="align: right"]51.53[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-17Sensor-23[/TD]
[TD="align: right"]51.53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sensor-26Sensor-18[/TD]
[TD="align: right"]51.53[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NEW-ROLL[/TD]
[TD]NEW-ROLL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] SHADE-PASSSensor-17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Typically when people log data into a worksheet they do not skip rows.

So from what I understand you will be entering data into columns A and B

So why are some rows in column B empty

So from what I understand first time you log in data you may skip over a cell B14 and B 28
But next time you enter data you want to fill in those empty cells with data.


Normally people would input data into cells B1 to B40
And next time enter data into cells B41 to B87

You asked for first empty cell in column B as most times people want to know what is the last row with data in column B

Show me the script you are now using to enter the data.

Or help me understand better.
Sorry if I'm a little slow at understanding.

 

[tvsramesha]

that's very educative suggestion from you, we are getting data from microcontroller to excel, appreciate your idea of moving fresh data into a new column, rather than continuing in the same column, that works for me, wondering how I, do that!

 

[My Aswer Is This]

that's very educative suggestion from you, we are getting data from microcontroller to excel, appreciate your idea of moving fresh data into a new column, rather than continuing in the same column, that works for me, wondering how I, do that!

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.