Macro to select variable start/end range?

[Montery]

Hi everyone! First time poster, and I did some searching but I'm very new to VBA so I thought I'd ask here as it seems like a friendly community.

What I would like to do is have a macro to select/copy a range of data. The trick is that the starting row is variable, based on a keyword search, and the ending row is the data, which is variable too. However, the columns are fixed.

Using the macro recorder, here's what I got:

Cells.Find(What:="Employee Number", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("A34:O34").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

The bit I get lost on is the Range("A34:O34").Select piece... I've been trying to find a way to reference the location of what the Cells statement finds, to the end of the last row that has data (Row 34 in this case).

I was thinking I need two functions:
1) To find "Employee Number" row
2) To find the last row with data'

And then I would simply replace Range("A34:O34").Select with Range(Function(1),Function(2)).Select.

But every time I try to use a function, I get runtime errors. Recognizing that my VBA skills are seriously lacking, any suggestions?

Thanks again!!

 

[JackDanIce]

Welcome to the board. Try:

Dim rng as Range
Dim x as Long
On Error Resume Next
Set rng = Cells.Find(What:="Employee Number", LookIn:=xlValues, LookAt:=xlWhole)
On Error Goto 0
If rng is Nothing Then
  MsgBox "Employee Number not found, please check and try again", vbExclamation, "Value not found!"
  Exit Sub
End If
x = cells(rows.count, rng.column).end(xlUp).Row
Range("A" & rng.row & ":O" & x).Copy

 

[Montery]

That's awesome, thanks so much!!

And thanks for the speedy reply!

 

[JackDanIce]

You're welcome