CODEWITHSUNDEEP
excelvba
Matt Larion
Matt Larion

Reputation: 11

Macro to copy and paste to Range of cells and then loop to the next range

I have a list of transactions that do not have the same number of rows. Some transactions have 3 rows, some may have more. Only the first row of each transaction has a transaction number. I need to find a way to copy the transaction number from the first row and paste it to the end of the rows that make up that transaction and then repeat it for the next transaction.

Does anyone have an easy suggestion on how to accomplish this? sample of transaction list

enter image description here The above is just a sample. There are 7000 transactions

Upvotes: 1

Views: 39

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Fill the Gaps

Sub FillTransactions()
    
    ' Read.
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rCount As Long: rCount = ws.UsedRange.Rows.Count - 1
    If rCount < 2 Then Exit Sub
    
    Dim rg As Range: Set rg = ws.Range("A2").Resize(rCount)
    
    Dim Data() As Variant: Data = rg.Value
    
    ' Modify.
    
    Dim r As Long, OldNum As Long, NewNum As Long
    
    For r = 1 To rCount
        NewNum = Data(r, 1)
        If NewNum = 0 Then Data(r, 1) = OldNum Else OldNum = NewNum
    Next r
    
    ' Write.
    
    rg.Value = Data

End Sub

Upvotes: 1

Related Questions