Using a formula in a macro

[Al @ Frontier]

I have the following macro (simplified, of course):
==================
Sub Macro1()
Macro1 Macro
Keyboard Shortcut: Ctrl+s
Range("A1:a15").Select
Selection.Copy
End Sub
====================
I would like to substitute a formula (match("the end",A1:a100)) for the
"a15", but can't find the correct syntax.

Can anyone help?

Thanks.

 

[Mike H]

Hi,

Is this what you mean

lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:a" & lastrow).Select


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Daryl S]

Al -

Substitute your range statement for these two:

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select