CODEWITHSUNDEEP
excelvbarows
Jason
Jason

Reputation: 15

How do I paste data until the next blank cell several times in single column?

I am trying to take a single column with several blocks of information (i.e. 6 lists separated by a space of 4-5 rows within the same column) and paste an identifier next to every entry in the lists. In this case, the identifier is the same, since this is a way to match all the entries within 1 block of information to a single name.

List with ID
enter image description here

Every list comprises the bonds that are within 1 basket, and hence in order to match each bond to their basket, I make the code enter the name of the basket next to every entry of the list.

The code goes to the first entry in the list, inputs an identifier in the cell immediately next to it (e.g. "ECB") and expands that same input downwards up until the last row. However, I would like the code to only expand until the last entry in the list (e.g. from I5 to I3457). The next piece of code (in this case "ECB Extended") would go from I13504 to I17046.

The problem arises when the Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)) code is executed in the first cell, it does not stop in the last entry of the list, but it goes down until the very last row of the entire sheet (e.g. I140454):

Problem
enter image description here

Sub GCPooling_Basket_Matching()

Macro3 Macro
Sheets("GC").Select

'ECB

    Range("I5").Select
    ActiveCell.FormulaR1C1 = "ECB"
    Range("I5").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))


'ECB Extended

    Range("I3504").Select
    ActiveCell.FormulaR1C1 = "EXT"
    Range("I3504").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))


'INT MXQ

    Range("I17204").Select
    ActiveCell.FormulaR1C1 = "MAXQ"
    Range("I17204").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))


'Equity

    Range("I19204").Select
    ActiveCell.FormulaR1C1 = "Equity"
    Range("I19204").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))    

End Sub

Does anyone know a way to expand "ECB" from I5 to I3457 and make it stop at I3457?

Upvotes: 0

Views: 89

Answers (3)

ASH
ASH

Reputation: 20302

See the link below for several ideas of how to handle this kind of thing.

https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/

Also, consider turning on the Macro Recorder before you click through the steps to get the code to do this kind of thing over and over and over.

Upvotes: 0

SJR
SJR

Reputation: 23081

If you don't know where the ranges start you could try an approach like this. I don't really understand how your codes fit with the number of blocks so that bit might need adjusting.

Sub x()

Dim r As Range, v As Variant, i As Long

v = Array("ECB", "EXT", "MAXQ", "Equity")

For Each r In Columns(1).SpecialCells(xlCellTypeConstants).Areas
    r.Offset(, 1) = v(i)
    i = i + 1
Next r

End Sub

enter image description here

Upvotes: 1

BenderIO
BenderIO

Reputation: 412

I think you can use an Offset to avoid your problem and use the XlDown on existing Data. For example if you have just A and B columns and want to fill ECB and ETF automatically on C like this:

enter image description here

You can use this code and you will avoid to use what BruceWayne told you to care about best practice:

Sub jason()

Range(Range("B5").Offset(0, 1), Range("B5").End(xlDown).Offset(0, 1)) = "ECB"
Range(Range("B22").Offset(0, 1), Range("B22").End(xlDown).Offset(0, 1)) = "ETF"

End Sub

Upvotes: 1

Related Questions