filling down automactically ?

[Daniel Rascoe]

Cell B1 has a formula which generates the row # of the last nonblank cell in
Column E. The current value of cell B1 in my spreadsheet is 10,012.

Cell G13 has a formula in it.

Cell H264 has a formula in it.

Cell AC767 has a formula in it.

What I want is for Excel to automatically fill down the formulas from G13,
H264 and AC767 to the row number in cell B1 automatically.

The result would be that G13 would be filled down to G10012, H264 would be
filled down to H10012 and AC767 would be filled down to AC10012.

I want Excel to do this automatically instead of me choosing the cells and
manually filling down.

Does anyone know how to accomplish this?



Daniel

 

[Bernie Deitrick]

Daniel,

You would need to use a macro, below. You could also use code like this from
an event.

HTH,
Bernie
MS Excel MVP

Sub DanielFill()
Range("G13").AutoFill Destination:=Range _
(Range("G13"), Range("G" & Range("B1").Value))
Range("H264").AutoFill Destination:=Range _
(Range("H264"), Range("H" & Range("B1").Value))
Range("AC767").AutoFill Destination:=Range _
(Range("AC767"), Range("AC" & Range("B1").Value))
End Sub

 

[Guest]

What sort of macro/function did you use to get the last row number to appear
in B1?

 

[Daniel Rascoe]

Cell B1 has the following array formula in it
{=MAX(IF(NOT(ISBLANK(Data!E2ata!E65526)),ROW(Data!E2ata!E65526),0))}

I have the raw data in a separate worksheet called "Data".

Daniel

 

[Daniel Rascoe]

Bernie,

Thanks for your help. After chatting with someone else, I decided I should
clear out the cells before filling down. He suggested the following Macro
which seems to work well.

Sub UpdateFormulas()
Range("G14:G65536").ClearContents
Range("H265:H65536").ClearContents
Range("AC768:AC65536").ClearContents
Range("G13:G" & Range("B1").Value).Formula = Range("G13").Formula
Range("H264:H" & Range("B1").Value).Formula = Range("H264").Formula
Range("AC767:AC" & Range("B1").Value).Formula = Range("AC767").Formula
End Sub

As I'm a newbie with Macros is comparing his method of filling down with
your a case of 6 of one versus a half dozen of the other? Once again, thanks
a lot.

Daniel

 

[Bernie Deitrick]

Daniel,

There are usually 10 different ways to achieve the same thing - OK, not
always, but yes, the code does the same thing.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Daniel,
I have tried your formula. I even added a sheet called "Data" and added
data to several cells in Column E to try to duplicate what you have.
However, I only get "2" as an answer. Would you mind double checking your
formula? What do you suppose I am doing different?

TIA
Papa

 

[Bernie Deitrick]

Papa,

That is an array formula that requires Ctrl-Shift-Enter instead of just
Enter.

HTH,
Bernie
MS Excel MVP

 

[Daniel Rascoe]

I got the information from this link
http://www.pcmag.com/article2/0,1759,1601676,00.asp

------------------------------------------------------------------------------------------