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

HTannenbaum

New Member
Joined
Feb 5, 2018
Messages
6
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

It's hard to answer definitively without seeing the data and the code but this statement:
Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Code:
Selection.End(xlDown)
then isn't the row number of that last line just
Code:
MsgBox Selection.End(xlDown).Row

:confused:

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.
 
Last edited:

HTannenbaum

New Member
Joined
Feb 5, 2018
Messages
6
I'm not sure how to post my worksheet here, but this is what I have. Again, any help is appreciated!

425.58123
234
345
456
456
234
2325
2
235
235
2
356
256
32456
3456
32456

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,577
Office Version
  1. 2010
Platform
  1. Windows
I'm not sure how to post my worksheet here, but this is what I have. Again, any help is appreciated!

425.58123
234
345
456
456
234
2325
2
235
235
2
356
256
32456
3456
32456

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
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

New Member
Joined
Feb 5, 2018
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Sep 5, 2004
Messages
1,672
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If the 425.58 is in cell A2, the following might work.
Code:
Range(Cells(2, 1), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 1)).FillDown
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

Watch MrExcel Video

Forum statistics

Threads
1,118,875
Messages
5,574,762
Members
412,617
Latest member
mlharris
Top