Select range using Endx1Down

[agcoleman]

I am trying to get a code that will search for a term, highlight the row that term appears in, then select the range between that term and the next time data shows up in that column.
Below is what I came up with so far. I know through recent research that activate and select aren't the best to use so if there is a better way I am open to it. Everything works up until the last row and then it gives me a run time 1004 error.

Sheets("Labor Report").Activate
Sheets("Labor Report").Range("A1").EntireColumn.Select
Selection.Find(What:="US Last code", After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
Selection.EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select

 

[DanteAmor]

Try this

Sub select_range()
  Dim f As Range
  
  Sheets("Labor Report").Activate
  Set f = Range("A:A").Find("US Last code", , xlValues, xlPart, xlByRows, xlNext, False)
  If Not f Is Nothing Then
    Range(f, f.End(xlDown)).Select
  End If
End Sub

Before run the macro:

After:

Comment if it is what you need, otherwise, describe with examples what you have and what you expect as a result.


I hope to hear from you very soon.
Cordially.
Dante Amor

 

[agcoleman]

Try this

Sub select_range()
  Dim f As Range
 
  Sheets("Labor Report").Activate
  Set f = Range("A:A").Find("US Last code", , xlValues, xlPart, xlByRows, xlNext, False)
  If Not f Is Nothing Then
    Range(f, f.End(xlDown)).Select
  End If
End Sub

Before run the macro:

View attachment 103630​

After:

View attachment 103631​

Comment if it is what you need, otherwise, describe with examples what you have and what you expect as a result.


I hope to hear from you very soon.
Cordially.
Dante Amor

Thanks for the reply unfortunately this didn't seem to get what i was looking for. I pasted a picture below for better reference. Once I get to the correct sheet I want to search in column A until i find "US Last code". I then want to highlight the entire row that term is on and then highlight all of the rows below it until the next time US Last code appears. Once I do that I am going to copy that selection and then paste it under the send line that US Last code appears on.

 

[DanteAmor]

I still don't understand what you need. That is why it is important that you put an image of what you have before running the macro and another image with the result you want.

Based on your image, what do you want to copy and where do you want to paste it?

Forget that you first want to select, simply explain, from your image, what you want to copy and where to paste.

It would help if you explain it in detail and with pictures.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

My guess is that the code shown (generally better to show the whole code unless extremely long) is in a worksheet module (not the 'Labor Repot' sheet's module) as that would cause that last line to give a '1004' error.

If that code needs to be in that other worksheet's module then this should fix that error.

Sheets("Labor Report").Activate
Sheets("Labor Report").Range("A1").EntireColumn.Select
Selection.Find(What:="US Last code", After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
Selection.EntireRow.Select
Sheets("Labor Report").Range(Selection, Selection.End(xlDown)).Select

Having said that, there is rarely a need to activate/select items to work with them in vba and doing so generally slows your code considerably.
If you wanted to look at alternatives to activating/selecting then please provide more details about just what you have and what you are trying to do as suggested by Dante.