Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
neilriches
 
Posts: n/a
Default 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   Report Post  
PY & Associates
 
Posts: n/a
Default

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   Report Post  
Myrna Larson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
Count nonblank AND fill color Purseus Excel Worksheet Functions 1 August 17th 05 07:13 PM
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
formula will not fill correctly due to odd number rows Barb Excel Worksheet Functions 3 May 10th 05 09:10 PM
Adding Rows to Master Sheet Excel Newbie New Users to Excel 1 December 23rd 04 11:56 PM


All times are GMT +1. The time now is 06:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"