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.
 

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
11,460
Office Version
365, 2010
Platform
Windows, 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
11,460
Office Version
365, 2010
Platform
Windows, Mobile
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
35,501
Office Version
2010
Platform
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
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,411
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
11,460
Office Version
365, 2010
Platform
Windows, Mobile

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top