VBA Loop to select then copy a range of cells based on value in column B

0 votes

I want a macro to loop over column B and copy the cells range from cell A of the first row to cell B of the last row for references that have the same first four numbers. I have references in column B.

i.e

Column A Column B
13/01/23 0501765
14/01/23 0501766
13/01/23 0502023
15/01/23 0512301

In this case, the code would first copy the range A1:B2, which I would then instruct it to paste (I know how to do this), after which it would repeat the loop and copy the range A3:B3, and so on.

Can someone help? I just want to know what the syntax would be for something like this.

I don’t know much about loops, here’s what I’ve come up with so far.

Dim WorkRng as Range

Set WorkRng = Range(“B:B”)

For i = 1 To WorkRng.Rows.Count
    If Left(WorkRng.Cells(i, 2).Value, 4) <> Left(WorkRng.Cells(i + 1, 2).Value, 4)
Mar 23, 2023 in Others by Kithuzzz
• 38,000 points
2,342 views

1 answer to this question.

0 votes

Try this:

Sub Macro2()
    Dim rg As Range
    Dim i As Integer
    Dim x As Integer
    Dim FirstThisRow As Integer
    Dim FirstNextRow As Integer
    Dim count As Integer

    Set rg = [c5].CurrentRegion
    On Error GoTo 1
    count = 0
    i = rg.Rows.count - 1

    For x = 1 To i
    FirstThisRow = Left(rg.Cells(x + 1, 2), 4)
    FirstNextRow = Left(rg.Cells(x + 2, 2), 4)

    If FirstThisRow = FirstNextRow Then
    count = count + 1
    Else
    1:
    Range(rg.Cells(x + 1, 1), rg.Cells(x + 1 - count, 2)).Copy
    Range("K4").Offset(x - count, 0).Select
    ActiveSheet.Paste
    count = 0

    End If
    Next x
    End Sub
answered Mar 23, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
3,783 views
0 votes
1 answer

VBA Help to find a column based on header value and cupy it to an other worksheet

You can break out the "copy column ...READ MORE

answered Jan 26, 2023 in Others by narikkadan
• 63,600 points
2,396 views
0 votes
1 answer

How do I copy a specific range of cells after I use AutoFilter in VBA?

Set the range of filtered data Set Rng ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,600 points
881 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,600 points
881 views
0 votes
1 answer

How to add if this cell = 0 skip and go next

Try this: Option Explicit Sub AutoMakeInv() ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,600 points
394 views
0 votes
1 answer

Automating production of graphs using VBA

You have to change: chrtname = Cells(i, 2).Value To: chrtname ...READ MORE

answered Jan 23, 2023 in Others by narikkadan
• 63,600 points
823 views
0 votes
1 answer

How can increase the speed of if statement in VBA Code?

Use a Dictionary Object. Option Explicit Sub PreencherO() ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,600 points
544 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,600 points
459 views
0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,600 points
744 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP