I hope I got your question right, this is not a formula. it is a VBA code.
VBA stands for Visual Basic for Applications which is a simple programing language for office applications (by Microsoft).
1st you need to open the VBA editor through the excel.
If you know nothing about this I would recommend you search for "excel VBA tutorial' on Google. it is very simple & you can lean very fast.
if you know a bit about the editor all you need to do is copy the code I posted into a module & run it
I will post the most updated macro I have so just copy it all into a module & it should work:
The macro uses this following sub & function:
1. This Sub is in order to play a sound when the macro finishes, it is very useful when it takes a long time for the macro to run (you can change the sound file to your taste:
Public Sub playfinishsound()
sndPlaySound32 "C:\WINDOWS\Media\Windows XP Shutdown.wav", 0&
2. this function will return the number of the last row that was used in the sheet
Function LastRow() As Long
Dim ix As Long
ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastRow = ix
This is the macro itself:
' Auto_fill_cells Macro
' the macro will run on the active cell column starting from the selected cell & _will fill in all the blanks
' cells with the 1st data above them until the end of the last row that was used in the sheet.
Dim rngend As Integer
Dim varCellValue As String
Dim n As Double
'find end of data in the sheet
rngend = LastRow()
'loop through the used cells
For n = ActiveCell.Row To rngend
If ActiveCell.Value <> "" Then
varCellValue = ActiveCell.Value
If ActiveCell = "" Then
ActiveCell.Value = varCellValue
this code uses excel 2007!