Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA: How to select a block of data 1

Status
Not open for further replies.

vugmail

Technical User
May 15, 2007
3
US
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
 
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.
 




Hi,
Code:
  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,

[glasses] [red][/red]
[tongue]
 




sorry, used the wrong object...
Code:
                 .Range(.Cells([b]d[/b].Row, "A"), .Cells([b]d[/b].Row, "B")).Copy


Skip,

[glasses] [red][/red]
[tongue]
 
Skip

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




that's what I get for not reading the OP more closely
Code:
                 .Range(.Cells(d.Row, "A"), .Cells(d.Row+1, "C")).Copy

Skip,

[glasses] [red][/red]
[tongue]
 
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(y) (again where C(y+1)<>C(y)) copy A(x+1):C(y), 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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top