How to apply a function to an entire column

[Barb @ Work]

I'm a new user to Excel so please be gentle with me. I'm using Excel version
2003.

A spreadsheet of raw data will be created each day via an export from
another tool and sent to me to clean it up and format it appropriately.

One thing I need to do is to calculate the elapsed time between the
date/time in two different columns (Bn minus An) and place that result into
Cn. If either Bn or An is blank, I'm placing a vaule of 0 00:00 into Cn.
Having read many of the postings here today, I already have this working
properly on the first data row in my "test" spreadsheet, row 2.

My "test" spreadsheet only has 50 data rows in it and since I'm working with
it interactively, I can easily replicate the formula in C2 to only the
populated rows of my spreadsheet by copying the formula in C2 and pasting it
to C3.....C51.

Since I need to apply the same formatting to the raw data spreadsheet I'll
receive each day, I'm going to put it all into an Excel macro. How do I apply
the formula in C2 to only the populated data rows in the spreadsheet, via the
macro, not knowing how many data rows are in the spreadsheet? That number
will vary day-to-day and will range in count from 500 to several thousand.

Sorry for being so lengthy. Your help is greatly appreciated! Barbara

 

[JE McGimpsey]

One way:

Public Sub FillDifferenceDown()
With ActiveSheet
With .Range("C2:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
.Formula = "=IF(COUNT(A2:B2)<2,0,B2-A2)"
.Value = .Value
.NumberFormat = "hh:mm:ss"
End With
End With
End Sub

If you'd rather keep the formulae, delete or comment out the

.Value = .Value

line.