Fill down to nonblank rows

[neilriches]

I need to create a macro that fills a formula down to the last row in a
worksheet.

The problem is the number of rows in the worksheet varies so I can't
simply use the basic fill down action.

Help!

 

[PY & Associates]

Try this

range(activecell,activecell.End(xlDown)).formula="=bla bla bla"

 

[Myrna Larson]

I see a conflict in your subject line and what you describe in the body of
your message.

To me, the subject line implies that you have something like data in A1:A25,
formulas in B1:B10, and in B26 to B?, and you want to fill B11:B25.

OTOH, from the message body I assume you have formulas only in B1:B10, and the
cells below B10 are blank.

If the situtation is the former, PY's solution will work. So will simply
double-clicking the fill handle in the lower right corner of the last filled
cell.

OTOH, if it's the 2nd situation -- no filled cells below those to be filled --
the macro will not work. It will fill to the bottom of the worksheet. So you
would need to identify the last row to be filled but looking at another
column, say A. In the example below, I assume you determine the last row of
data by looking at column A, and the formula to be filled down is in column B.


Dim LastFormula As Long
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastFormula = .Cells(.Rows.Count, 2).End(xlUp).Row
If LastFormula < LastRow Then
.Cells(1, 2).Resize(LastRow - LastFormula + 1, 1).FillDown
End If
End With