Working out first and last column and row in a range

[Peter Rooney]

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

 

[K Dales]

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

 

[Peter Rooney]

Thanks very much for this! )

Pete

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

 

[Peter Rooney]

It just goes to show that you should always search for an answer here before
re-asking the question! This may be a bit unwieldy, but it's easy to read and
understand - just how I like my answers!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ModSheet As Worksheet
Set ModSheet = ActiveWorkbook.Worksheets("PrefixEntries")

Dim ModRange As Range
Set ModRange = ModSheet.Range("ModRange")

Dim TopLeft, TopRight, BottomLeft, BottomRight As Range
Set TopLeft = ModRange(1, 1)
Set TopRight = ModRange(1, ModRange.Columns.Count)
Set BottomLeft = ModRange(ModRange.Rows.Count, 1)
Set BottomRight = ModRange(ModRange.Rows.Count, ModRange.Columns.Count)

'Dim AddressMessage As String
'AddressMessage = "Top Left = " & TopLeft.Address & ", Top Right = " &
TopRight.Address & _
", Bottom Left = " & BottomLeft.Address & ", Bottom Right = " &
BottomRight.Address
'MsgBox (AddressMessage)

On Error GoTo ws_exit:

Application.EnableEvents = False

If Target.Column >= TopLeft.Column Then
If Target.Column <= TopRight.Column Then
If Target.Row >= TopLeft.Row Then
If Target.Row <= BottomLeft.Row Then
Target.Value = "XYZ" & Target.Formula
End If
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Patrick Molloy]

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]

Patrick,

This is VERY neat - thank you very much! )
Pete

 

[Peter Rooney]

Patrick,

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

Regards

Pete