Help needing the proper formula

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
Hi All,

I'm building a macro that will delete and format a spreadsheet. It's a read only so every month new info will be pasted in and the macro will be run through a button and it will format it and delete any info thats not needed. The problem I'm having is that in column A there is a fund name lets say AAA on line 3 then column B and C are filled with codes of equities and names of equities respectively but on line 50 a new fund starts and again on 98 and so on. Every month the funds are different sizes and what I am trying to do is make column A equal to the correct fund. example:


AAAEquity codeName of equity
Equity codeName of equity
Equity codeName of equity
Equity codeName of equity

<TBODY>
</TBODY>

What i want the macro to do is fill in column A (among other things) like this:

How do i achieve this with a formula without overwriting the other funds?is there a formula i can you for the whole of column A that can pull in the write fund the whole way down the page?

Thanks for your time.
AAAEquity CodeName Of Equity
AAAEquity CodeName Of Equity
AAAEquity CodeName Of Equity

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,181
Try:
Code:
Sub FillBlanks()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("A2:A" & LastRow)
        If rng = "" Then
            rng = Cells(rng.Row - 1, 1)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top