Help needing the proper formula

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top