Thanks very much for this! :o)

Pete



"K Dales" wrote:

Range("ModRange").Rows.Count gives the number of rows
Range("ModRange").Columns.Count gives the number of columns

"Peter Rooney" wrote:

Afternoon, all!

I'm working on a change macro that will prefix an entry that is made
anywhere in the worksheet range "ModRange" wirh "xyz"

I'm using nested IF statements, based around absolute row and column
numbers, but it's a bit clumsy and my aim is to base the macro around the
"ModRange" range, as against absolute references, so that if I insert or
delete any rows or columns to the top/left of "ModRange", I don't have to
respecify the range of cells to be modified in the macro.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ModRange As Range
Set ModRange = Sheets("PrefixEntries").Range("ModRange")

On Error GoTo ws_exit:

Application.EnableEvents = False

If Target.Column 1 Then
If Target.Column < 5 Then
If Target.Row 3 Then
If Target.Row < 14 Then
Target.Value = "XYZ" & Target.Formula
End If
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

I'm trying to figure out how to determine the first and last column and row
for a worksheet range, which will then allow my code to refer to them.

Can anyone help me out, please?

Thanks in advance

Pete