Selecting the first cell in a selection, then the row of that cell

[gbutton]

So, my sheet is sorted in column H and the following code will select the first row with different information

Columns("H:H").Select
Selection.ColumnDifferences(ActiveCell).Select

Now, I need to select that row, copy it to a new tab, then find the next column difference. I seem to be having trouble figuring out how to select the row.

I'm pretty new, any help would be appreciated.

 

[storm8]

Sub test()
                
    'declare 3 ranges
    Dim r As Range, c As Range, m As Range
    
    'set range r to be your desired output
    Set r = Columns("a:a").ColumnDifferences(ActiveCell)
    
    'exit if no results
    If r Is Nothing Then Exit Sub
    
    'for each cell in results
    For Each c In r.Cells
        
        'set m = range of 10 cells on a row where the result is found, starting from the result
        Set m = ActiveSheet.Cells(c.Row, c.Column).Resize(1, 10)
        
        'move somewhere else, on the same row as the result was found
        m.Copy Destination:=Sheets(2).Cells(c.Row, 1)
    Next c    
End Sub

avoid selecting stuff unless you rally want to. Its probably the slowest method ever seen in vba

 

[gbutton]

Excellent, thank you! I had no idea those commands existed and they are much faster.

One more question: it seems to be pulling every row when I need it to just pull the first row of each column difference.

So if this is my data:

Ad group <tbody> </tbody>	CTR <tbody> </tbody>
Accessories_Phrase_Shutter Dogs <tbody> </tbody>	1.65% <tbody> </tbody>
Accessories_Phrase_Shutter Dogs <tbody> </tbody>	0.00% <tbody> </tbody>
Appliques_Broad_Acanthus <tbody> </tbody>	9.09% <tbody> </tbody>
Appliques_Broad_Acanthus <tbody> </tbody>	8.00% <tbody> </tbody>
Appliques_Broad_Acanthus <tbody> </tbody>	0.00% <tbody> </tbody>
Appliques_Broad_Acanthus <tbody> </tbody>	0.00% <tbody> </tbody>
Appliques_Broad_Acanthus <tbody> </tbody>	0.00% <tbody> </tbody>
Appliques_Broad_Affordable <tbody> </tbody>	1.00% <tbody> </tbody>
Appliques_Broad_Affordable <tbody> </tbody>	0.00% <tbody> </tbody>
Appliques_Broad_Affordable <tbody> </tbody>	0.00% <tbody> </tbody>
Appliques_Broad_Affordable <tbody> </tbody>	0.00% <tbody> </tbody>
Appliques_Broad_Affordable <tbody> </tbody>	0.00% <tbody> </tbody>

<tbody>
</tbody>

This is my desired output:

Ad group <tbody> </tbody>	CTR <tbody> </tbody>
Accessories_Phrase_Shutter Dogs <tbody> </tbody>	1.65% <tbody> </tbody>
Appliques_Broad_Acanthus <tbody> </tbody>	9.09% <tbody> </tbody>
Appliques_Broad_Affordable <tbody> </tbody>	1.00% <tbody> </tbody>

<tbody>
</tbody>

It looks like the answer would be here:

    'for each cell in results
    For Each c In r.Cells

But I'm not sure how to just choose that first one.

 

[storm8]

your request was to slect that ROW and copy it.
if im correct, you just want to copy that cell?

that is done in the resize statement. change Resize(1,10) to Resize(1,1) or you just can delete it

 

[gbutton]

Sorry, I guess I'm not conveying myself well.

I do want to copy over the row and that works well. The problem I'm having is that when I run the macro it's pulling over every row. I need to just pull the first row of each adgroup.

Does that make sense?

 

[storm8]

I have not really worked with ColumnDifferences but I assume that Selection.ColumnDifferences(ActiveCell).Select "selects" every cell in range that is different from the value in selected cellIs that what you wan to do or do you want to filter data based on some criteria?Look at my other post here http://www.mrexcel.com/forum/excel-questions/752460-copy-multiple-rows.html#post3694911</pre>

 

[gbutton]

I want to filter some data.

So, before running this macro I'm sorting my sheet by "Adgroup" then "CTR". The table I posted earlier is sorted like this, it has all adgroups grouped together with the best performing (highest CTR) at the top. I want to select the highest performer of each adgroup (top row of each adgroup) and move it over to the new sheet.

My thought was to isolate an adgroup with ColumnDifferences then select the topmost row of that selection but maybe column difference isn't the correct function to use. It's just the only thing I could think of.

 

[storm8]

Im afraid that that is nowhere near your original post :D
here's a code that will do the trick (assuming your data is sorted)

Sub copy()


    Dim destRw As Integer
    Dim r As Range
                        
    Set r = Cells(1).Resize(100, 2) 'select source range, 100 rows, 2 colls from range("A1")
    
    Dim c As Range
    For Each c In r.Columns(1).Cells
        If WorksheetFunction.CountIfs(Sheets(2).Columns(1), c.Value) = 0 Then
            destRw = destRw + 1
                        
            c.Resize(1, 2).copy Destination:=Sheets(2).Cells(destRw, 1)
            
        End If
    Next c


End Sub

 

[gbutton]

Perfect! Exactly what I'm looking for. Thanks also for being patient with me, I'm still getting used to macros and how to describe them.

This is great, you are great!

 

[gbutton]

You've already helped me a bunch, but I'm curious to know if you could help me understand how this works.

    For Each c In r.Columns(1).Cells
        If WorksheetFunction.CountIfs(Sheets(2).Columns(1), c.Value) = 0 Then
            destRw = destRw + 1

This is the only part I'm having trouble understanding. How does it know where one adgroup starts and another begins?

Also, if I'm looking for the adgroup data in column(8) instead of column(1) how would I format my resize to select everything to the left and right of that column (the whole row).

Thanks!