Select the cell that contains that important information.
Then give it a nice name by typing something like this in the name box (to the
left of the formula bar):
myRowNumber
(remember to hit enter when finish your typing!)

Then change the code to something like:

rng = "W" & ws.Range(myRowNumber).Value
(I like to qualify the property!)



Victor Delta wrote:

"Victor Delta" wrote in message
...
"Sheeloo" wrote in message
...
Try
Sub macro()
Set ws = ThisWorkbook.Worksheets("Clients")
ws.Activate
rng = "W" & ws.Range("A100")
ws.Range(rng).Select
End Sub


This worked fine until I inserted some additional rows into the spreadsheet
the other day and therefore needed the range formula reference to change to
A105 rather than A100.

Is there a way to improve the formula so this happens automatically in
future?

TIA

V

--

Dave Peterson