Macro to Autofill all blank cells in a column

[dencarter]

Hi guys -

I have another Excel macro problem.

I have a spreadsheet with a column that's formatted like this:

6575
blank
blank
blank
4457
blank
blank
blank
blank
3355
blank
blank

Obviously the 'blanks' are blank cells and there are about 10,000 rows in the sheet.

I would like to autofill the blank cells so that the value above them is repeated, so it would actually look like this:

6575
6575
6575
6575
4457
4457
4457
4457
4457
3355
3355
3355

I'm sorry if this is a really obvious one, but I've searched and can't find anything to help me... If anyone can point me in the right direction I would really appreciate it.

Cheers,
Den

 

[canefan17]

Try

Sub FillBl()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("A1:A" & LR)
    With .SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
    End With
    .Value = .Value
End With
End Sub

Ok I have the exact same question as OP except I need the numbers to autofill UP

So

BLANK
BLANK
BLANK
123010
BLANK
BLANK
BLANK
BLANK
13399
BLANK
BLANK
67543

NEEDS TO READ

123010
123010
123010
123010
13399
13399
13399
13399
13399
67543
67543
67543


And if you do have a code for me can you please tell me what exactly I need to do with it? I'm a newbie to macros.

 

[VoG]

This assumes the numbers are in column A.

Press ALT + F11

Insert > Module

Paste in

Sub FillBl()
Dim LR As Long
LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("A1:A" & LR)
    With .SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[1]C"
    End With
    .Value = .Value
End With
End Sub

Press ALT + F11

Then run the FillBl macro.

 

[xlHammer]

Assuming your list starts at A2, insert a column next to it (which will become column B), put this formula in B2,

=IF(A2="",B1,A2)

then copy and paste this formula all the way down column B until you get to the end of your list. You will then have your desired list in column B. If necessary you can copy this and paste special values over the list in column A.

Any help?

To fill up using the helper column method above just use

=IF(A2="",B3,A2)

instead and fill down to the bottom of your data in column A