Need to copy a formula down to the next blank line that varies.

[HTannenbaum]

I have a spreadsheet containing different groups of data, each of which vary in number of lines, separated by single blank lines. The number of groups also varies from month to month. I can find the last line of a particular group using 'SelectionEnd(xlDown).Select' with no problem but i don't know how to capture the row number of that last row. I need that to AutoFill a formula in the adjoining column. I plan on looping through this logic depending on the number of "groups' of lines I find. Any help will be appreciated.

 

[Andrew Fergus]

Hi

It's hard to answer definitively without seeing the data and the code but this statement:

MsgBox Selection.Row

Will show the row number of your current selection. This value can then be used to construct your formula.
e.g. .Range("L" & Selection.Row).Formula = "=K" & Selection.Row.

If the selection row is 6 then this will put the formula =K6 into cell L6. Modify to suit.

I trust this helps.
Andrew

 

[MARK858]

I can find the last line of a particular group using 'SelectionEnd(xlDown).Select' with no problem but i don't know how to capture the row number of that last row.

If as you state you can find the last line of each group each time with

Selection.End(xlDown)

then isn't the row number of that last line just

MsgBox Selection.End(xlDown).Row

You will probably find if you explain in more detail what you are doing (as Andrew implied) there is a more efficient way of achieving what you want rather than use Select/Selection.

 

[HTannenbaum]

I'm not sure how to post my worksheet here, but this is what I have. Again, any help is appreciated!

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]425.58[/TD]
[TD="width: 64, align: right"]123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]345[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2325[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]235[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]235[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]356[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]32456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]32456[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

What column is the data with the spaces in?
Are the numbers constants or the result of formulas?
Are the blank cells truly blank or are they empty strings from the result of formulas using ""?
What column is the column with the formulas?
What is the first cell with the formula?
What is the formula in the first cell?

 

[Rick Rothstein]

I'm not sure how to post my worksheet here, but this is what I have. Again, any help is appreciated!

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]425.58[/TD]
[TD="width: 64, align: right"]123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]345[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2325[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]235[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]235[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]356[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]32456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]32456[/TD]
[/TR]
</tbody>[/TABLE]

What row number does the above data start on?

What columns is the data in?

What formula are you trying to put in the next column?

 

[HTannenbaum]

Cell A1 contains the formula.
Column B contains several sets of data that vary in number of rows each month.
I am trying to propagate the formula in A1 through the last row in column B.

Some background:

The formula is actually in the first row of column B and varies with each group of entries in column B. There is a blank line after the last entry in each group in column B.

I can move the formula to the correct corresponding row in column A for the first group. It is here that I have the problem. I don't know how to AutoFill to a variable number of rows.

After that formula is populated in column A for each entry in column B, I am assuming I will know the address of the last row and can easily determine the starting row of the next group in column B and can go through this process again.

When I record a macro to do this, the macro uses absolute addresses for the AutoFill function. That's where I get lost. I don't know how to modify that for the variable number of rows needed.

(I downloaded "MrExcel HTML Maker" and there were no instructions! I tried everything I knew as to how to use it and couldn't. That's a problem for another day. I did try to figure it out to give you a better look at the spreadsheet but have the original problem to solve first.)

 

[jolivanes]

If the 425.58 is in cell A2, the following might work.

Range(Cells(2, 1), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 1)).FillDown

 

[MARK858]

@HTannenbaum, I am afraid you still haven't answered the question both Rick and myself asked which is what is the formula in the first cell i.e. A1 in your case.

Can you just also clarify whether column B is constants or formulas (by your last post I think they are formulas which rules out one option but I need to make sure)?


Original instructions on how to Install Html maker

About the update to the Html maker