Deleting rows in undefined column length

Hi everyone, I'm Travis and I am new here. I came here in hopes to solve a problem that has been eating time out of my work schedule. I have spent countless hours searching for a solution that suited my exact needs but when I try to piece together codes or manipulate them in anyway I always end up with errors and it is very frustrating, hopefully some of you can help me here.

At work we have a computerized piece of machinery that collects log data as it runs and puts that data into a text file. I can open the text file in Excel and create a log report. The problem comes in the form of deleting data. There is a column that records the machines steps and records them from 0 to 6. Once the sixth step is complete the logger will log zeros until it is shut off.

IE:
0
0
1
1
2
2
3
3
4
4
5
5
6
6
0
0

Essentially all of the data with the step number 0 is useless and has to be removed from the report. The logger only runs for about 30 seconds at a time and in the time frame it can record data filling 300+ rows, the amount of rows being different every time.

With that being said I a trying to create a macro that will read through the entire column to the last filled row and delete every row, entirely, that contains a step number of 0. I know this post is a little messy. I just got through thumbing through forum after forum pulling my hair out so my thoughts aren't all here at the moment. Any ideas would be greatly appreciated.

Thanks for your reply oeldere. I am just getting into work and I will give it a try just now. I am looking at my workbook now, and column C is the column with the step count so when you can get back to me can we try to specify that column C is the one that needs to be checked for zeros? I will post back with results from the code you already gave me. Thank you.

These are the changes I made and it works great.

Please Login or Register  to view this content.

Now to take it a step further I would like to take the values from column B and subtract the second cell's value from the last cell's value and put the result in the last empty cell in column B. After all of the zeros have been removed. I don't know if this can be written in the same macro or if I would need to write a separate macro for this action.

So you want to take the ' last cell B value' MINUS 'second cell B value' ?

this can be done in the same macro, after 'next r' but before 'End Sub'

you can use this line of code

var = ActiveSheet.UsedRange.Rows.Count

where 'var' is just a variable name, therefore var = number of rows used in the active sheet.

then you could do something like

Cells(var+1,2) = Cells(var,2) - Cells(2,2)

In words, this would take the last cell of column B (row number 'var') MINUS second value of column B (row 2) and put this value into 'last row +1' column B

First off, thank you for explaining how the code works. I hope to go through and learn how to write macros from scratch after I get this sorted out.

The macro puts the number in the correct place, however, I for got to mention the the numbers are in time format, i.e, 13:32:07. When I use the macro it changes the value to a decimal form. How can I tell the macro to keep the new number in the time format?

if you go to an empty space on your worksheet eg A1 and type in 01:02:03
go to the cell A2 and type 04:05:06
go to the cell A3 and type =TEXT(A2-A1,"hh:mm:ss"), hit enter and it will give you the time difference between the two values

I am stilll trying to figure out how to use this in vba, maybe somebody will jump in with a solution before I do

Got it! My code looks like this now:

Please Login or Register  to view this content.

So this gives you exactly what you were after ? Result.