Excel VBA: How to select a block of data 1

[vugmail]

I have a worksheet as below. I wrote the VBA code, look for value changes in column C then select and copy the selection to another worksheet and save a prn file. ie. I'll three blocks to copy A1:C2; A3:C3; and A5:C6

A B C
1 843779.9 262620.2 S159110
2 843710.7 268730.7 S159110
3 833313.5 262420.2 S159111
4 830603.9 268563.5 S159112
5 825349.7 268494.2 S159112

Could anyone help me to identify the cells in column A C(cellA) in the below code:

Sub test()
Dim d As Range
Dim cellA, cellB As String

For Each d In Intersect(ActiveSheet.UsedRange, Range("C:C"))
If d.Offset(0, 0) <> d.Offset(1, 0) Then
cellB = d.Offset(0, 0).Address(RowAbsolute:=False, ColumnAbsolute:=False)
cellA = ????

Range(cellB, cellA).Select
Selection.Copy
'Paste to another worksheet and save as prn: I got this part.
End If

Next d
End Sub


Many thanks,
TU

 

[Fenrirshowl]

Hi there - it's morning and I'm yet to have a coffee , but I think the answer is:
Put cellA equal to cell A1 prior to the For Loop.
Then, just before the End If, you need to update this to the value of cellB.offset(1,-2) (i.e. the A column on the row below your column C area reference). When you cycle through again, this value will be ready for use.

Now, must get coffee.

 

[vugmail]

I got it. Thanks.

 

[SkipVought]

Hi,

  Sub test()
    Dim d As Range
    
    With ActiveSheet
        For Each d In Intersect(.UsedRange, .Range("C:C"))
            If d.Value <> d.Offset(1, 0).Value Then
                 .Range(.Cells(r.Row, "A"), .Cells(r.Row, "B")).Copy
                'Paste to another worksheet and save as prn: I got this part.
            End If
            
        Next d
    End With
  End Sub

Skip,

_[red][/red]

 

[vugmail]

Much simpler. Thanks Skip.

 

[SkipVought]

sorry, used the wrong object...

                 .Range(.Cells([b]d[/b].Row, "A"), .Cells([b]d[/b].Row, "B")).Copy

Skip,

_[red][/red]

 

[Fenrirshowl]

Skip

Wouldn't that result in 5 pastes, line by line, rather than 3 blocks (as TU mentioned in his original post)?

 

[SkipVought]

that's what I get for not reading the OP more closely

                 .Range(.Cells(d.Row, "A"), .Cells(d.Row+1, "C")).Copy

Skip,

_[red][/red]

 

[Fenrirshowl]

But wouldn't that copy "S159111" and "S159112" at the same time? Also, how would it copy the data in row 1?

My understanding of the problem is based on the idea it is to scroll down the c column starting at C1. Copy data from A1 to C(x) (Column C, x th row) where C(x+1)<>C(x) and paste, somewhere. Next repeat from C(x+1) to C (again where C(y+1)<>C) copy A(x+1):C, then paste [presumably to another location] and so on.

That was my reasoning for defining the upper left area to copy in the first run (as a type of anchor) and then updating that position when the new row where the value changes in C has been identified.

Apologies TU if I misunderstood - I said it was early and I needed coffee.