Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA setting Ranges
I would like to change the first Cell and the last Cell of a Range using
VBA I have this code; Dim MyRange As Range Set MyRange = Selection lets say the selection was ($A$1:$H$3), so I now have a MyRange of $A$1:$H $3 and using; MsgBox "My Range is " & MyRange.Address I get a message that says that this is true. Now I want to change the first Cell ($A$1) or the last Cell ($H$3) to something different so I have tried these variations; MyRange = Range(MyRange(1, 1).Address, H13) - Also tried using 'Set' Set MyRange = Range("H3", "K8") but using the message after either of these lines shows that the original range limit is still applying. Can anyone tell me how to set the first or last cell properties using VBA ? Any help appreciated Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA setting Ranges
On 21 Jul 2013 21:14:53 GMT, Malcolm Hind wrote:
I would like to change the first Cell and the last Cell of a Range using VBA I have this code; Dim MyRange As Range Set MyRange = Selection lets say the selection was ($A$1:$H$3), so I now have a MyRange of $A$1:$H $3 and using; MsgBox "My Range is " & MyRange.Address I get a message that says that this is true. Now I want to change the first Cell ($A$1) or the last Cell ($H$3) to something different so I have tried these variations; MyRange = Range(MyRange(1, 1).Address, H13) - Also tried using 'Set' Set MyRange = Range("H3", "K8") but using the message after either of these lines shows that the original range limit is still applying. Can anyone tell me how to set the first or last cell properties using VBA ? Any help appreciated Thanks Here are some illustrations. This Macro should be in a regular module To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. =============================== Option Explicit Sub RangeSetting() Dim MyRange As Range Set MyRange = Selection MsgBox "My Range is " & MyRange.Address 'Remove 1st and last cell Set MyRange = Range(MyRange(RowIndex:=2), MyRange(RowIndex:=MyRange.Rows.Count - 1)) MsgBox "My Range is " & MyRange.Address Set MyRange = Range("H3", "K8") MsgBox "My Range is " & MyRange.Address 'now change it to H4:K7 Set MyRange = MyRange.Offset(rowoffset:=1).Resize(rowsize:=MyRan ge.Rows.Count - 2) MsgBox "My Range is " & MyRange.Address End Sub ========================= =============================== |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA setting Ranges
Ron Rosenfeld wrote in
: MyRange(RowIndex:=2), MyRange(RowIndex:=MyRange.Rows.Count - 1 Ron, Thanks for that input - I have a couple of (sort of) follow up questions but I will post them seperately. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA setting Ranges
Further to Ron's`suggestion, perhaps...
Dim MyRange As Range Set MyRange = Selection With MyRange .Cells(1) = 123: .Cells(,Cells.Count) = 456 End With 'MyRange -OR- simply... With Selection .Cells(1) = 123: .Cells(,Cells.Count) = 456 End With 'Selection -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Scope of name ranges to particular worksheet | Excel Discussion (Misc queries) | |||
dynamic setting of data ranges | Charts and Charting in Excel | |||
help with setting up dynamic name ranges | Excel Worksheet Functions | |||
Setting ranges | Excel Programming | |||
Setting PrintArea by using Named Ranges | Excel Programming |