Making condition dynamic?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/05/2006 by Alex_Posatskiy
'

'
Range("X9:AD127").Select
Range("X9:AF127").Select
Range("AF9").Activate
Selection.Copy
Range("W9").Select
ActiveSheet.Paste
Range("AF9:AF127").Select
Selection.ClearContents
Range("W4").Select
With Worksheets("Asphalt").Range("w7")
.Value = .Value + 7
End With
End Sub

The "127" has changed and will constantly change. Row127 was the last row, but now 174 is. I want the code to leave the "127" variable to the length of the chart.

Originally Posted by Otto Moehrbach

There are a number of ways of doing this. Which way fits you depends on the
layout of your data. What would you say to a person if you were telling him
how to determine what rows to select? Post your code when you respond. Not
the file. Just the pertinent code. HTH Otto
"aposatsk" <[email protected]> wrote in
message news:[email protected]...
>
> I have a macro made for a spreadsheet. It used to work, until I began
> adding new rows. Since the macro was told to select, say, A1:A20, and
> now i have an extra row, that row will not be selected. Is there any
> way to make this condition A1:A'X', leaving X a variable, hence
> allowing the macro to work with any number of rows?
>
>
> --
> aposatsk
> ------------------------------------------------------------------------
> aposatsk's Profile:
> http://www.excelforum.com/member.php...o&userid=36709
> View this thread: http://www.excelforum.com/showthread...hreadid=571762
>