Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working out first and last column and row in a range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working out first and last column and row in a range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working out first and last column and row in a range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working out first and last column and row in a range
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 "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working out first and last column and row in a range
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working out first and last column and row in a range
Patrick,
This is VERY neat - thank you very much! :o) 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code not working within hidden column range | Excel Discussion (Misc queries) | |||
CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions | |||
Working with range | Excel Programming | |||
Create named range for each column not working? | Excel Programming | |||
Transfering VBA Array Column Range to Excel Column Range | Excel Programming |