excel macro column auto-fill

Question

I have a spreadsheet which I am cleaning and using macros to help. My column 'C' has temperature data. Like with all data, there is some missing. How would I write a macro that would auto-fill the missing spot with previous data?

For example:

  C                                       C

1  37                                   1  37
2  35                                   2  35
3                   -------->           3  35  
4  37                                   4  37
5  36                                   5  36

The spot C3 has been filled with C2's data.

Thank you for your help.

is it possible to have two empty cells in contiguous rows (say, `35,,,36`)? — Dmitry Pavliv, Feb 17 '14 at 12:27

score 3 · Answer 1 · answered Feb 17 '14 at 12:58

3

Do you really need VBA for this?

Do this

Select Col C
Press Ctrl + G
Click on Special
Next Click on Blanks
Click Ok
All Empty cells are now selected. Press the = key and then the Up arrow key
Lastly press Ctrl+Tab+Enter and you are done.

ScreenShot

enter image description here

answered Feb 17 '14 at 12:58

Siddharth Rout

147,039
17
206
250

+1! I thought about using in VBA formula from your another answer `[C2:C10] = [INDEX(IF(C2:C10="",C1:C9,C2:C10),)]`, but it works only in case when there is no two empty cells in contiguous rows and my conscience would be guilty:) – Dmitry Pavliv Feb 17 '14 at 13:03
1

@simoco: `my conscience would be guilty:) – simoco 49 secs ago` Not about just my post but if you use any other post as a reference, you should always give that person a credit in your post. Then you will notice that you will never feel guilty again ;) For [EXAMPLE](http://stackoverflow.com/questions/21769948/excel-vba-capitalizing-all-selected-cells-in-column-on-double-click/21773083#21773083) – Siddharth Rout Feb 17 '14 at 13:08

score 2 · Accepted Answer · answered Feb 17 '14 at 12:33

2

Give this a try:

Sub FixC()
    Dim N As Long, i As Long
    N = Cells(Rows.Count, "C").End(xlUp).Row
    For i = 2 To N
        If Cells(i, "C") = "" Then
            Cells(i, "C") = Cells(i - 1, "C")
        End If
    Next i
End Sub

answered Feb 17 '14 at 12:33

Gary's Student

95,722
10
59
99

+ 1 For a vba solution. – Siddharth Rout Feb 17 '14 at 12:58

score 0 · Answer 3 · answered Feb 17 '14 at 12:34

How would I write that macro:
This contains only snippets.

loop over all cells in the column:
for each cell in ActiveSheet.Columns(1).Cells

if the cell value is not empty -> save the value to a variable
If cell.value <> Empty then lastCellValue = cell.value

if the cell value is empty -> write the saved cell value into the cell
Else cell.value = lastCellValue

also:
if more than x (e.g. 20) cells in a row were empty, break from loop

excel macro column auto-fill

3 Answers3