VBA help - Fill down with changing start and end cells

[mkyp27]

Any help is much appreciated, I am learning VBA on the go and cannot seem to work through this issue.

Thanks!

Example Below:

Column A Column B

Portfolio 1:
AAPL
DIS
YHOO

Portfolio 2:
AAPL FORMULA (=A7&" US Equity")
FB
TWTR
XOM

Portfolio 3:
COP
KO
WMT


I am looking to write a code that fills down the formula only for the 2nd portfolio (down to XOM) and is able to adjust as the start and end of portfolio 2 shift depending on the number of holdings. I am able to locate where the formula begins using this code:

With Columns("I")
.Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues).Activate
End With

However, where I am having issues is defining this to stop at the row of XOM (ie. up until the next blank in column A from the start of the fill). At the moment I have only enabled it to fill all the way down to the bottom of the sheet, rather than stopping at the end of this set of rows. I cannot figure out how to alter this code to get my desired results:

With Columns("I")
.Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues).Activate
Range(ActiveCell, ActiveCell.End(xlDown)).FillDown
End With

 

[Roderick_E]

consider determining the last populated row?


lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

 

[mkyp27]

The issue with that is the last populated row would return the row with WMT in it in my example, whereas I only want to fill down to XOM. I have used this before when I want to fill down to the last row in a sheet, but I am attempting to fill only a defined range (from AAPL to XOM in my example) and this range is constantly changing in new versions of the file that I run weekly.

The column I am filling has no values in it except for the formula (in AAPL's row), which is why I used the Find to activate the first row, but I have yet to find a solution to filling down to the end of this group of rows, stopping at the next blank in Column A. Something along the lines of fill from First Cell With Value (AAPL #2) to Next Blank in Column A (XOM).

Any ideas? I appreciate the response

I have a spreadsheet example, but I am unable to attach it