Copying Formulas into a range where blanks exist.

[LutonBarry]

This'll be easy for some of you. I import a large spreadsheet of data some 13,000 lines with over 26 plus columns each day into a workbook. The number of lines each day varies so I know how to import and append the data succesfully. However there are some formulas in my spreadsheet that I need to copy down into unoccupied cells. Typically using the jpeg below I would doubleclick the lower right corner of cell E2 which copies the formulas down as long as in this instance cell D3 was not empty.

I did wite some code to select the range and the blanks and have a character inserted to be removed later so that would work. But with such a large number of cells and many of which are blank this takes some time, is there an easier way for the code to know it has to copy the formulas in cell E2 down to cell E8 or cell E20 the following day if more lines are added.

13717

[excelliot]

Check this if it helps:

http://www.ozgrid.com/Excel/excel-fill-blank-cells.htm

[LutonBarry]

Thanks I was using the fill blanks formula but this looks interesting and will test this one out many thanks.

[SamT]

Sub SamT_Paste()
Range("E1").Copy
Range(Range("E1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 4)).PasteSpecial xlFormulas
End Sub


Sub SamT_Fill()
Range(Range("E1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 4)).FillDown
End Sub


Sub SamT_Intersect()
'Shakey
Intersect(UsedRange, Range("E:E")).FillDown
End Sub

Sub SamT_Region()
'Possible
Intersect(Range("E1").CurrentRegion, Range("E:E")).FillDown
End Sub

Sub SamT_Fastest_Maybe()
Range(Cells(Rows.Count, 5).End(xlUp), Cells(Rows.Count, 1).End(xlUp).Offset(0, 4)).FillDown
End Sub

[mancubus]

as an alternative (i oftenly use similar codes in my projects)



With Worksheets("Sheet1").UsedRange
With .Columns("E").Offset(1).Resize(.Rows.Count - 1)
.Formula = "=VLOOKUP(A2,Sheet2!$A$1:$K$100,2,0)"
.Value = .Value 'convert formulas to formula results
End With
End With

[LutonBarry]

Tried the code below and it stops at the first message box asking you to select a single column. If I go into the Module it is at the first Exit Sub statement in bold I can go back in and step through it but it will not continue without that intervention. I have altered it to so it enters a ^ character instead of the contents of the cell above and it does work but not without stepping into the module.


Sub FillBlanks()

Dim rRange1 As Range, rRange2 As Range

Dim iReply As Integer

If Selection.Cells.Count = 1 Then

MsgBox "You must select your list and include the blank cells", _

vbInformation, "OzGrid.com"

Exit Sub

ElseIf Selection.Columns.Count > 1 Then

MsgBox "You must select only one column", _

vbInformation, "OzGrid.com"

Exit Sub

End If



Set rRange1 = Range(Selection.Cells(1, 1), _

Cells(65536, Selection.Column).End(xlUp))



On Error Resume Next

Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)

On Error GoTo 0



If rRange2 Is Nothing Then

MsgBox "No blank cells Found", _

vbInformation, "OzGrid.com"

Exit Sub

End If


rRange2.FormulaR1C1 = "^"



iReply = MsgBox("Convert to Values", vbYesNo + vbQuestion, "OzGrid.com")

If iReply = vbYes Then rRange1 = rRange1.Value

End Sub

[SamT]

Tried the code below
Did you select more than one cell?
Did the selection have any Blank Cells?
Did you only select one column?

That is an OzGrid procedure. Did you ask them for help?

Did you even look at any of the suggestions in the posts above?

[snb]

If you use a Table (Excel > 2003) it wil be done automatically: no code required.

[LutonBarry]

SamT, Many, many thanks the code Fastest maybe works a treat. Many thanks again