Select a range until a specific value y a specific column

[jb01001]

I have a table, I need to select a range from a5:l until one row before when the value "e" starts in column H. Value "e" varies every time new data is entered. As selected in the example

The Table is previouly sorted to leave "e" in the final rows.

Please consider the name of the sheet as Ill need to duplicate this for every sheet.

Workbooks("prueba captura nueva - Copy (2).xlsm").Worksheets("carga av")

Once selected, Ill paste special in another workbook, but I have that part done already.

 

[kevin9999]

Sub Find_e()
    Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("carga av")
    Set rng = ws.Range(ws.Cells(5, 1), ws.Cells(ws.Columns("L").Find("e").Row - 1, 12))
    rng.Select  '<< to test
End Sub

 

[kevin9999]

Correction

Sub Find_e()
   Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("carga av")
    Set rng = ws.Range(ws.Cells(5, 1), ws.Cells(ws.Columns("H:H").Find("e").Row - 1, 12))
    rng.Select  '<< to test
End Sub

 

[jb01001]

Correction

Sub Find_e()
   Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("carga av")
    Set rng = ws.Range(ws.Cells(5, 1), ws.Cells(ws.Columns("H:H").Find("e").Row - 1, 12))
    rng.Select  '<< to test
End Sub

Hello

It returned with this selection...

Thanks!!

 

[kevin9999]

Sorry, forgot the Lookat:=xlWhole

Sub Find_e()
   Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("carga av")
    Set rng = ws.Range(ws.Cells(5, 1), ws.Cells(ws.Columns("H:H").Find("e", Lookat:=xlWhole).Row - 1, 12))
    rng.Select  '<< to test
End Sub