Select range

[d5itup]

I am trying to select a range of cells using a macro. The number of columns in the selection will be constant (A-C), but the number of rows will vary. I am hoping for it to select the number of columns based on similar values in column A. The values in column A should be grouped together, so basically I am looking for something that will detect the first value that does not match and select everything above.

Eventually I will be doing some coping and pasting in another sheet, deleting what was selected, and repeating but I already have that code.

 

[MARK858]

I am hoping for it to select the number of columns based on similar values in column A

Explain exactly what you mean by "similar values". Ideally post some sample data (there are some ways to post data on the board in the links in my signature.

Finally in the quote above did you mean "number of rows" instead of "number of columns" (you already stated that the columns are fixed).

 

[d5itup]

Explain exactly what you mean by "similar values". Ideally post some sample data (there are some ways to post data on the board in the links in my signature.

Finally in the quote above did you mean "number of rows" instead of "number of columns" (you already stated that the columns are fixed).

Yes sorry. For your second question I did mean number of rows. On the screenshot below I am looking for some code that would select the Range A2:C5 based on the value of column A for those cells is all "12345". It would always be columns A:C, but the number of rows might change. The plan is eventually to copy what is selected to another sheet, come back to this workbook, delete the already copied rows, and repeat the process

 

[MARK858]

What you are asking can be easily achieved using the autofilter manually but I have posted a code version below (change the sheetnames to suit).

BTW. please look at the ways of posting screenshots in my signature block. When you post an image like you did in your previous post we can't copy and paste the data into Excel which means if we want to test our code we have to manually retype the data (and a lot of posters (me included) won't normally spend our time doing this).

Sub Filterit()
Application.ScreenUpdating = False
    With Sheets("Sheet1").Range("A1:C" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="12345"

        On Error Resume Next
        With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
            .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .EntireRow.Delete
            On Error GoTo 0
        End With
        .AutoFilter

    End With
    Application.ScreenUpdating = True
End Sub