Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill down to nonblank rows
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! -- neilriches ------------------------------------------------------------------------ neilriches's Profile: http://www.excelforum.com/member.php...o&userid=27161 View this thread: http://www.excelforum.com/showthread...hreadid=466734 |
#2
|
|||
|
|||
Try this
range(activecell,activecell.End(xlDown)).formula=" =bla bla bla" -- Regards PY & Associates "neilriches" wrote in message ... 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! -- neilriches ------------------------------------------------------------------------ neilriches's Profile: http://www.excelforum.com/member.php...o&userid=27161 View this thread: http://www.excelforum.com/showthread...hreadid=466734 |
#3
|
|||
|
|||
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 On Mon, 12 Sep 2005 04:26:01 -0500, neilriches wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Count nonblank AND fill color | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
formula will not fill correctly due to odd number rows | Excel Worksheet Functions | |||
Adding Rows to Master Sheet | New Users to Excel |