RE: Working out first and last column and row in a range

Patrick,

My earlier response was bounced, so just to repet myself - this is VERY neat
- thank you very much!

Regards

Pete

"Patrick Molloy" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Range("ModRange"), Target) Is Nothing Then
> Application.EnableEvents = False
> Target.Value = "XYZ" & Target.Formula
> Application.EnableEvents = True
> End If
>
> "Peter Rooney" wrote:
>
> > Afternoon, all!
> >
> > I'm working on a change macro that will prefix an entry that is made
> > anywhere in the work*** 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 work*** range, which will then allow my code to refer to them.
> >
> > Can anyone help me out, please?
> >
> > Thanks in advance
> >
> > Pete
> >
> >
.