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

 

[VoG]

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

 

[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?

 

[dencarter]

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

Hi there -

Thanks for taking the time to look at this...

I changed
With Range("A1:A" & LR)
to
With Range("C1:C" & LR)
because the column with the data is actually C.

When I run the Macro I get:

Run-time error '1004':
No cells were found

Any ideas?

 

[VoG]

Are they blanks or do they contain spaces. Try selecting all the blanks, hitting the Delete key, then running the macro.

 

[dencarter]

Are they blanks or do they contain spaces. Try selecting all the blanks, hitting the Delete key, then running the macro.

Great - progress! But now I just get #REF! in each of the newly-filled cells... Sorry to be a pain, really appreciate your time.

 

[VoG]

I've just tried this

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

with your example data in column C and it worked.

If your workbook doesn't contain sensitive information, could you upload it to www.box.net and provide a link.

 

[xlHammer]

I tried Peter's code and got the #REF! errors if the first cell was blank (ie C1 was blank), I have no idea why it would do this but putting a heading in C1 cured it!?

?

 

[bolandd]

How about this? Assuming column A has your data, press F5, Click special, choose blank cells. Your cursor should be in the first blank cell (in my case B1). Type the formula "=A1" and hit control enter.

You may have to manually do the last occurrence...

 

[dencarter]

I tried Peter's code and got the #REF! errors if the first cell was blank (ie C1 was blank), I have no idea why it would do this but putting a heading in C1 cured it!?

?

Hi guys - this solved the problem perfectly! It was just because there were blank rows above the data... Strange but I am now very happy! Thank you VoG - brilliant!

xlHammer - your =IF solution also worked a treat.

Thanks very much!