How to select multiple ranges in Excel with vbs

Stefan Mueller · 04-19-2005, 09:06 AM

The following vbs code opens Excel and selects the ranges A1-A10 and C2-C8:
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select

But how can I do such a selection with variables?

e.g.
SelectionPart1_Column = 1
SelectionPart1_RowFrom = 1
SelectionPart1_RowTo = 10

SelectionPart2_Column = 3
SelectionPart2_RowFrom = 2
SelectionPart2_RowTo = 8

The following command selects only the first part (A1-A10):
objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
SelectionPart1_Column)).Select

But how can I add to the already selected part 1 also part 2 (C2-C8)?

Any help is very appreciated
Stefan

Don Guillett · 04-19-2005, 10:06 AM

this might be easier in vba
Range("a1:a10,h2:h8").Select

--
Don Guillett
SalesAid Software
donaldb@281.com
"Stefan Mueller" <seekware-remove-@yahoo.com> wrote in message
news:3ckd52F6o8mddU1@uni-berlin.de...
> The following vbs code opens Excel and selects the ranges A1-A10 and
C2-C8:
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Workbooks.Add
> objExcel.Visible = True
> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
>
> But how can I do such a selection with variables?
>
> e.g.
> SelectionPart1_Column = 1
> SelectionPart1_RowFrom = 1
> SelectionPart1_RowTo = 10
>
> SelectionPart2_Column = 3
> SelectionPart2_RowFrom = 2
> SelectionPart2_RowTo = 8
>
> The following command selects only the first part (A1-A10):
> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
> SelectionPart1_Column)).Select
>
> But how can I add to the already selected part 1 also part 2 (C2-C8)?
>
> Any help is very appreciated
> Stefan
>
>

Tom Ogilvy · 04-19-2005, 11:06 AM

Dim rng1 as Excel.Range
Dim rng2 as Excel.Range
Dim rng3 as Excel.Range
Dim sh1 as Excel.Worksheet
set sh1 = objExcel.Activesheet
set rng1 = sh1.Range(sh1.Cells(SelectionPart1_RowFrom, _
SelectionPart1_Column), she.Cells(SelectionPart1_RowTo, _
SelectionPart1_Column))
set rng2 = sh1.Range(sh1.Cells(SelectionPart2_RowFrom, _
SelectionPart2_Column), sh1.Cells(SelectionPart2_RowTo, _
SelectionPart2_Column))

set rng3 = ObjExcel.Union(rng1,rng2)
rng3.Select

of course there usually is no reason to select as you can just refer to the
ranges

--
Regards,
Tom Ogilvy

"Stefan Mueller" <seekware-remove-@yahoo.com> wrote in message
news:3ckd52F6o8mddU1@uni-berlin.de...
> The following vbs code opens Excel and selects the ranges A1-A10 and
C2-C8:
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Workbooks.Add
> objExcel.Visible = True
> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
>
> But how can I do such a selection with variables?
>
> e.g.
> SelectionPart1_Column = 1
> SelectionPart1_RowFrom = 1
> SelectionPart1_RowTo = 10
>
> SelectionPart2_Column = 3
> SelectionPart2_RowFrom = 2
> SelectionPart2_RowTo = 8
>
> The following command selects only the first part (A1-A10):
> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
> SelectionPart1_Column)).Select
>
> But how can I add to the already selected part 1 also part 2 (C2-C8)?
>
> Any help is very appreciated
> Stefan
>
>

Leo Heuser · 04-19-2005, 11:06 AM

Stefan

One way.
Remember to set a reference to the
Excel object library from the VBA-editor in Word with
Tools > References

'Leo Heuser, 19-4-2005
Dim objExcel As Excel.Application
Dim SelRange As Excel.Range
Dim MyBook As Excel.Workbook

Set objExcel = New Excel.Application
Set MyBook = objExcel.Workbooks.Add
objExcel.Visible = True

With MyBook.ActiveSheet
Set SelRange = objExcel.Union(.Range("A1:A10"), .Range("C2:C8"))
End With

SelRange.Value = 1234

Set objExcel = Nothing

End Sub

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Stefan Mueller" <seekware-remove-@yahoo.com> skrev i en meddelelse
news:3ckd52F6o8mddU1@uni-berlin.de...
> The following vbs code opens Excel and selects the ranges A1-A10 and
> C2-C8:
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Workbooks.Add
> objExcel.Visible = True
> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
>
> But how can I do such a selection with variables?
>
> e.g.
> SelectionPart1_Column = 1
> SelectionPart1_RowFrom = 1
> SelectionPart1_RowTo = 10
>
> SelectionPart2_Column = 3
> SelectionPart2_RowFrom = 2
> SelectionPart2_RowTo = 8
>
> The following command selects only the first part (A1-A10):
> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
> SelectionPart1_Column)).Select
>
> But how can I add to the already selected part 1 also part 2 (C2-C8)?
>
> Any help is very appreciated
> Stefan
>
>

Stefan Mueller · 04-19-2005, 06:06 PM

Great, .Union was what I was looking for the whole morning.

Therefore the following command does my requested selection:
objExcel.Union(objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
SelectionPart1_Column)),
objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart2_RowFrom,
SelectionPart2_Column), objExcel.Cells(SelectionPart2_RowTo,
SelectionPart2_Column))).Select

Many thanks for your help.
Stefan

PS: Does anyone know if it's even possible to select a cell/range in a not
active worksheet?
Today I'm doing
objExcel.Worksheets("My Worksheet 1").Activate
objExcel.Cells(1, 1).Select
objExcel.Worksheets("My Worksheet 2").Activate

But this is flickering. Therefore I'm looking for something like
objExcel.Worksheets("My Worksheet 1").Cells(1, 1).Select

"Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
news:eZbD0%23ORFHA.1392@TK2MSFTNGP10.phx.gbl...
> Stefan
>
> One way.
> Remember to set a reference to the
> Excel object library from the VBA-editor in Word with
> Tools > References
>
> 'Leo Heuser, 19-4-2005
> Dim objExcel As Excel.Application
> Dim SelRange As Excel.Range
> Dim MyBook As Excel.Workbook
>
> Set objExcel = New Excel.Application
> Set MyBook = objExcel.Workbooks.Add
> objExcel.Visible = True
>
> With MyBook.ActiveSheet
> Set SelRange = objExcel.Union(.Range("A1:A10"), .Range("C2:C8"))
> End With
>
> SelRange.Value = 1234
>
> Set objExcel = Nothing
>
> End Sub
>
> --
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
> "Stefan Mueller" <seekware-remove-@yahoo.com> skrev i en meddelelse
> news:3ckd52F6o8mddU1@uni-berlin.de...
>> The following vbs code opens Excel and selects the ranges A1-A10 and
>> C2-C8:
>> Set objExcel = CreateObject("Excel.Application")
>> objExcel.Workbooks.Add
>> objExcel.Visible = True
>> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
>>
>> But how can I do such a selection with variables?
>>
>> e.g.
>> SelectionPart1_Column = 1
>> SelectionPart1_RowFrom = 1
>> SelectionPart1_RowTo = 10
>>
>> SelectionPart2_Column = 3
>> SelectionPart2_RowFrom = 2
>> SelectionPart2_RowTo = 8
>>
>> The following command selects only the first part (A1-A10):
>> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
>> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
>> SelectionPart1_Column)).Select
>>
>> But how can I add to the already selected part 1 also part 2 (C2-C8)?
>>
>> Any help is very appreciated
>> Stefan
>>
>>
>
>
>
>
>

Tom Ogilvy · 04-19-2005, 09:06 PM

No it is not possible, but as I said, you usually don't need to select.

--
Regards,
Tom Ogilvy

"Stefan Mueller" <seekware-remove-@yahoo.com> wrote in message
news:3clbhdF6q3lanU1@uni-berlin.de...
> Great, .Union was what I was looking for the whole morning.
>
> Therefore the following command does my requested selection:
>
objExcel.Union(objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowF
rom,
> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
> SelectionPart1_Column)),
> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart2_RowFrom,
> SelectionPart2_Column), objExcel.Cells(SelectionPart2_RowTo,
> SelectionPart2_Column))).Select
>
> Many thanks for your help.
> Stefan
>
>
> PS: Does anyone know if it's even possible to select a cell/range in a not
> active worksheet?
> Today I'm doing
> objExcel.Worksheets("My Worksheet 1").Activate
> objExcel.Cells(1, 1).Select
> objExcel.Worksheets("My Worksheet 2").Activate
>
> But this is flickering. Therefore I'm looking for something like
> objExcel.Worksheets("My Worksheet 1").Cells(1, 1).Select
>
>
>
> "Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
> news:eZbD0%23ORFHA.1392@TK2MSFTNGP10.phx.gbl...
> > Stefan
> >
> > One way.
> > Remember to set a reference to the
> > Excel object library from the VBA-editor in Word with
> > Tools > References
> >
> > 'Leo Heuser, 19-4-2005
> > Dim objExcel As Excel.Application
> > Dim SelRange As Excel.Range
> > Dim MyBook As Excel.Workbook
> >
> > Set objExcel = New Excel.Application
> > Set MyBook = objExcel.Workbooks.Add
> > objExcel.Visible = True
> >
> > With MyBook.ActiveSheet
> > Set SelRange = objExcel.Union(.Range("A1:A10"), .Range("C2:C8"))
> > End With
> >
> > SelRange.Value = 1234
> >
> > Set objExcel = Nothing
> >
> > End Sub
> >
> > --
> > Best Regards
> > Leo Heuser
> >
> > Followup to newsgroup only please.
> >
> > "Stefan Mueller" <seekware-remove-@yahoo.com> skrev i en meddelelse
> > news:3ckd52F6o8mddU1@uni-berlin.de...
> >> The following vbs code opens Excel and selects the ranges A1-A10 and
> >> C2-C8:
> >> Set objExcel = CreateObject("Excel.Application")
> >> objExcel.Workbooks.Add
> >> objExcel.Visible = True
> >> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
> >>
> >> But how can I do such a selection with variables?
> >>
> >> e.g.
> >> SelectionPart1_Column = 1
> >> SelectionPart1_RowFrom = 1
> >> SelectionPart1_RowTo = 10
> >>
> >> SelectionPart2_Column = 3
> >> SelectionPart2_RowFrom = 2
> >> SelectionPart2_RowTo = 8
> >>
> >> The following command selects only the first part (A1-A10):
> >> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
> >> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
> >> SelectionPart1_Column)).Select
> >>
> >> But how can I add to the already selected part 1 also part 2 (C2-C8)?
> >>
> >> Any help is very appreciated
> >> Stefan
> >>
> >>
> >
> >
> >
> >
> >
>
>

Stefan Mueller · 04-20-2005, 06:06 PM

I don't need to select a range?
Okay, but how can I draw a chart if I don't select the range before?

Today I'm using this code:
objExcel.Worksheets("My Sheet").Activate
objExcel.Union(objExcel.ActiveSheet.Range(objExcel.Cells(1, 1),
objExcel.Cells(10, 1)), objExcel.ActiveSheet.Range(objExcel.Cells(1, 3),
objExcel.Cells(10, 3))).Select

objExcel.Charts.Add
objExcel.ActiveChart.ChartType = 65 '* 65 -> Charttype:
xlLineMarkers
objExcel.ActiveChart.Location 2, "Charts"
objExcel.ActiveChart.PlotBy = 2
objExcel.ActiveChart.HasTitle = True
objExcel.ActiveChart.HasDataTable = True
objExcel.ActiveChart.DataTable.ShowLegendKey = True
objExcel.ActiveChart.DataTable.Font.Size=8
objExcel.ActiveSheet.Shapes("Chart " & Directory).Width = ChartSizeX
objExcel.ActiveSheet.Shapes("Chart " & Directory).Height = ChartSizeY
objExcel.ActiveSheet.Shapes("Chart " & Directory).Left = ChartPosX
objExcel.ActiveSheet.Shapes("Chart " & Directory).Top = ChartPosY

Stefan

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:udF%2351TRFHA.2228@TK2MSFTNGP12.phx.gbl...
> No it is not possible, but as I said, you usually don't need to select.
>
> --
> Regards,
> Tom Ogilvy
>
> "Stefan Mueller" <seekware-remove-@yahoo.com> wrote in message
> news:3clbhdF6q3lanU1@uni-berlin.de...
>> Great, .Union was what I was looking for the whole morning.
>>
>> Therefore the following command does my requested selection:
>>
> objExcel.Union(objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowF
> rom,
>> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
>> SelectionPart1_Column)),
>> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart2_RowFrom,
>> SelectionPart2_Column), objExcel.Cells(SelectionPart2_RowTo,
>> SelectionPart2_Column))).Select
>>
>> Many thanks for your help.
>> Stefan
>>
>>
>> PS: Does anyone know if it's even possible to select a cell/range in a
>> not
>> active worksheet?
>> Today I'm doing
>> objExcel.Worksheets("My Worksheet 1").Activate
>> objExcel.Cells(1, 1).Select
>> objExcel.Worksheets("My Worksheet 2").Activate
>>
>> But this is flickering. Therefore I'm looking for something like
>> objExcel.Worksheets("My Worksheet 1").Cells(1, 1).Select
>>
>>
>>
>> "Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
>> news:eZbD0%23ORFHA.1392@TK2MSFTNGP10.phx.gbl...
>> > Stefan
>> >
>> > One way.
>> > Remember to set a reference to the
>> > Excel object library from the VBA-editor in Word with
>> > Tools > References
>> >
>> > 'Leo Heuser, 19-4-2005
>> > Dim objExcel As Excel.Application
>> > Dim SelRange As Excel.Range
>> > Dim MyBook As Excel.Workbook
>> >
>> > Set objExcel = New Excel.Application
>> > Set MyBook = objExcel.Workbooks.Add
>> > objExcel.Visible = True
>> >
>> > With MyBook.ActiveSheet
>> > Set SelRange = objExcel.Union(.Range("A1:A10"), .Range("C2:C8"))
>> > End With
>> >
>> > SelRange.Value = 1234
>> >
>> > Set objExcel = Nothing
>> >
>> > End Sub
>> >
>> > --
>> > Best Regards
>> > Leo Heuser
>> >
>> > Followup to newsgroup only please.
>> >
>> > "Stefan Mueller" <seekware-remove-@yahoo.com> skrev i en meddelelse
>> > news:3ckd52F6o8mddU1@uni-berlin.de...
>> >> The following vbs code opens Excel and selects the ranges A1-A10 and
>> >> C2-C8:
>> >> Set objExcel = CreateObject("Excel.Application")
>> >> objExcel.Workbooks.Add
>> >> objExcel.Visible = True
>> >> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
>> >>
>> >> But how can I do such a selection with variables?
>> >>
>> >> e.g.
>> >> SelectionPart1_Column = 1
>> >> SelectionPart1_RowFrom = 1
>> >> SelectionPart1_RowTo = 10
>> >>
>> >> SelectionPart2_Column = 3
>> >> SelectionPart2_RowFrom = 2
>> >> SelectionPart2_RowTo = 8
>> >>
>> >> The following command selects only the first part (A1-A10):
>> >> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
>> >> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
>> >> SelectionPart1_Column)).Select
>> >>
>> >> But how can I add to the already selected part 1 also part 2 (C2-C8)?
>> >>
>> >> Any help is very appreciated
>> >> Stefan
>> >>
>> >>
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>

Tom Ogilvy · 04-21-2005, 10:06 AM

Didn't review each of these, but I bet you won't find much selecting in
them:

I previously showed you how to do the union without selecting, but you
ignored that, so don't know if you will bother with these either.

http://support.microsoft.com/default...b;en-us;184273
ACC97: How to Use Automation to Create a Microsoft Excel Chart

http://support.microsoft.com/default...b;en-us;202169
ACC2000: Using Automation to Create a Microsoft Excel Chart
(Pretty much the same article as above)

http://support.microsoft.com/default...b;en-us;157940
XL97: How To Create a Dynamic Chart Using Visual Basic

http://support.microsoft.com/default...b;en-us;109575
XL: Cannot Use Array as Source Argument with SeriesCollection

http://support.microsoft.com/default...b;en-us;213653
XL2000: Cannot Use Array as Source Argument with SeriesCollection

http://support.microsoft.com/default...b;en-us;172114
XL: Cannot Use Array of Data Points with Extend Method

http://support.microsoft.com/default...b;en-us;213687
XL2000: Cannot Use Array of Data Points with Extend Method

Another sample (under the assumption that more is better even if not
directly on topic):

http://support.microsoft.com/default...b;en-us;186219
XL97: Excel Quits Unexpectedly Running Macro That Creates Chart

http://support.microsoft.com/default...kb;en-us;12326
XL: Visual Basic Module to Create Gantt Chart
(uses chartwizard rather than setsourcedata)

http://support.microsoft.com/default...b;en-us;137016
XL: Macro to Extract Data from a Chart

http://support.microsoft.com/default...kb;en-us;14136
XL: VB Code to Automatically Set Min and Max Scale for Y- Axis

http://support.microsoft.com/default...b;en-us;126367
XL: Using the Range Method to Convert String to a Range
(example of getting the source range from a chart series)

http://support.microsoft.com/default...b;en-us;139662
XL: How to Use a Visual Basic Macro to Create a Bubble Chart

http://support.microsoft.com/default...b;en-us;161513
XL: Macro to Add Labels to Points in an XY (Scatter) Chart

http://support.microsoft.com/default...b;en-us;213814
XL2000: Macro to Extract Data from a Chart

http://support.microsoft.com/default...b;en-us;161858
XL97: How to Trap Events for an Embedded Chart

--
Regards,
Tom Ogilvy

"Stefan Mueller" <seekware-remove-@yahoo.com> wrote in message
news:3cnvo4F6l1ac2U1@uni-berlin.de...
> I don't need to select a range?
> Okay, but how can I draw a chart if I don't select the range before?
>
> Today I'm using this code:
> objExcel.Worksheets("My Sheet").Activate
> objExcel.Union(objExcel.ActiveSheet.Range(objExcel.Cells(1, 1),
> objExcel.Cells(10, 1)), objExcel.ActiveSheet.Range(objExcel.Cells(1, 3),
> objExcel.Cells(10, 3))).Select
>
> objExcel.Charts.Add
> objExcel.ActiveChart.ChartType = 65 '* 65 -> Charttype:
> xlLineMarkers
> objExcel.ActiveChart.Location 2, "Charts"
> objExcel.ActiveChart.PlotBy = 2
> objExcel.ActiveChart.HasTitle = True
> objExcel.ActiveChart.HasDataTable = True
> objExcel.ActiveChart.DataTable.ShowLegendKey = True
> objExcel.ActiveChart.DataTable.Font.Size=8
> objExcel.ActiveSheet.Shapes("Chart " & Directory).Width = ChartSizeX
> objExcel.ActiveSheet.Shapes("Chart " & Directory).Height =
ChartSizeY
> objExcel.ActiveSheet.Shapes("Chart " & Directory).Left = ChartPosX
> objExcel.ActiveSheet.Shapes("Chart " & Directory).Top = ChartPosY
>
> Stefan
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:udF%2351TRFHA.2228@TK2MSFTNGP12.phx.gbl...
> > No it is not possible, but as I said, you usually don't need to select.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Stefan Mueller" <seekware-remove-@yahoo.com> wrote in message
> > news:3clbhdF6q3lanU1@uni-berlin.de...
> >> Great, .Union was what I was looking for the whole morning.
> >>
> >> Therefore the following command does my requested selection:
> >>
> >
objExcel.Union(objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowF
> > rom,
> >> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
> >> SelectionPart1_Column)),
> >> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart2_RowFrom,
> >> SelectionPart2_Column), objExcel.Cells(SelectionPart2_RowTo,
> >> SelectionPart2_Column))).Select
> >>
> >> Many thanks for your help.
> >> Stefan
> >>
> >>
> >> PS: Does anyone know if it's even possible to select a cell/range in a
> >> not
> >> active worksheet?
> >> Today I'm doing
> >> objExcel.Worksheets("My Worksheet 1").Activate
> >> objExcel.Cells(1, 1).Select
> >> objExcel.Worksheets("My Worksheet 2").Activate
> >>
> >> But this is flickering. Therefore I'm looking for something like
> >> objExcel.Worksheets("My Worksheet 1").Cells(1, 1).Select
> >>
> >>
> >>
> >> "Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
> >> news:eZbD0%23ORFHA.1392@TK2MSFTNGP10.phx.gbl...
> >> > Stefan
> >> >
> >> > One way.
> >> > Remember to set a reference to the
> >> > Excel object library from the VBA-editor in Word with
> >> > Tools > References
> >> >
> >> > 'Leo Heuser, 19-4-2005
> >> > Dim objExcel As Excel.Application
> >> > Dim SelRange As Excel.Range
> >> > Dim MyBook As Excel.Workbook
> >> >
> >> > Set objExcel = New Excel.Application
> >> > Set MyBook = objExcel.Workbooks.Add
> >> > objExcel.Visible = True
> >> >
> >> > With MyBook.ActiveSheet
> >> > Set SelRange = objExcel.Union(.Range("A1:A10"),
..Range("C2:C8"))
> >> > End With
> >> >
> >> > SelRange.Value = 1234
> >> >
> >> > Set objExcel = Nothing
> >> >
> >> > End Sub
> >> >
> >> > --
> >> > Best Regards
> >> > Leo Heuser
> >> >
> >> > Followup to newsgroup only please.
> >> >
> >> > "Stefan Mueller" <seekware-remove-@yahoo.com> skrev i en meddelelse
> >> > news:3ckd52F6o8mddU1@uni-berlin.de...
> >> >> The following vbs code opens Excel and selects the ranges A1-A10 and
> >> >> C2-C8:
> >> >> Set objExcel = CreateObject("Excel.Application")
> >> >> objExcel.Workbooks.Add
> >> >> objExcel.Visible = True
> >> >> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
> >> >>
> >> >> But how can I do such a selection with variables?
> >> >>
> >> >> e.g.
> >> >> SelectionPart1_Column = 1
> >> >> SelectionPart1_RowFrom = 1
> >> >> SelectionPart1_RowTo = 10
> >> >>
> >> >> SelectionPart2_Column = 3
> >> >> SelectionPart2_RowFrom = 2
> >> >> SelectionPart2_RowTo = 8
> >> >>
> >> >> The following command selects only the first part (A1-A10):
> >> >> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
> >> >> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
> >> >> SelectionPart1_Column)).Select
> >> >>
> >> >> But how can I add to the already selected part 1 also part 2
(C2-C8)?
> >> >>
> >> >> Any help is very appreciated
> >> >> Stefan
> >> >>
> >> >>
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Stefan Mueller · 05-04-2005, 12:06 PM

Hello

In the meantime I've found a solution:

objExcel.ScreenUpdating = False '* Do not update the screen

< do the selection on the other worksheet >

objExcel.ScreenUpdating = True '* Update the screen again

Stefan

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:udF%2351TRFHA.2228@TK2MSFTNGP12.phx.gbl...
> No it is not possible, but as I said, you usually don't need to select.
>
> --
> Regards,
> Tom Ogilvy
>
> "Stefan Mueller" <seekware-remove-@yahoo.com> wrote in message
> news:3clbhdF6q3lanU1@uni-berlin.de...
>> Great, .Union was what I was looking for the whole morning.
>>
>> Therefore the following command does my requested selection:
>>
> objExcel.Union(objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowF
> rom,
>> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
>> SelectionPart1_Column)),
>> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart2_RowFrom,
>> SelectionPart2_Column), objExcel.Cells(SelectionPart2_RowTo,
>> SelectionPart2_Column))).Select
>>
>> Many thanks for your help.
>> Stefan
>>
>>
>> PS: Does anyone know if it's even possible to select a cell/range in a
>> not
>> active worksheet?
>> Today I'm doing
>> objExcel.Worksheets("My Worksheet 1").Activate
>> objExcel.Cells(1, 1).Select
>> objExcel.Worksheets("My Worksheet 2").Activate
>>
>> But this is flickering. Therefore I'm looking for something like
>> objExcel.Worksheets("My Worksheet 1").Cells(1, 1).Select
>>
>>
>>
>> "Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
>> news:eZbD0%23ORFHA.1392@TK2MSFTNGP10.phx.gbl...
>> > Stefan
>> >
>> > One way.
>> > Remember to set a reference to the
>> > Excel object library from the VBA-editor in Word with
>> > Tools > References
>> >
>> > 'Leo Heuser, 19-4-2005
>> > Dim objExcel As Excel.Application
>> > Dim SelRange As Excel.Range
>> > Dim MyBook As Excel.Workbook
>> >
>> > Set objExcel = New Excel.Application
>> > Set MyBook = objExcel.Workbooks.Add
>> > objExcel.Visible = True
>> >
>> > With MyBook.ActiveSheet
>> > Set SelRange = objExcel.Union(.Range("A1:A10"), .Range("C2:C8"))
>> > End With
>> >
>> > SelRange.Value = 1234
>> >
>> > Set objExcel = Nothing
>> >
>> > End Sub
>> >
>> > --
>> > Best Regards
>> > Leo Heuser
>> >
>> > Followup to newsgroup only please.
>> >
>> > "Stefan Mueller" <seekware-remove-@yahoo.com> skrev i en meddelelse
>> > news:3ckd52F6o8mddU1@uni-berlin.de...
>> >> The following vbs code opens Excel and selects the ranges A1-A10 and
>> >> C2-C8:
>> >> Set objExcel = CreateObject("Excel.Application")
>> >> objExcel.Workbooks.Add
>> >> objExcel.Visible = True
>> >> objExcel.ActiveSheet.Range("A1:A10,C2:C8").Select
>> >>
>> >> But how can I do such a selection with variables?
>> >>
>> >> e.g.
>> >> SelectionPart1_Column = 1
>> >> SelectionPart1_RowFrom = 1
>> >> SelectionPart1_RowTo = 10
>> >>
>> >> SelectionPart2_Column = 3
>> >> SelectionPart2_RowFrom = 2
>> >> SelectionPart2_RowTo = 8
>> >>
>> >> The following command selects only the first part (A1-A10):
>> >> objExcel.ActiveSheet.Range(objExcel.Cells(SelectionPart1_RowFrom,
>> >> SelectionPart1_Column), objExcel.Cells(SelectionPart1_RowTo,
>> >> SelectionPart1_Column)).Select
>> >>
>> >> But how can I add to the already selected part 1 also part 2 (C2-C8)?
>> >>
>> >> Any help is very appreciated
>> >> Stefan
>> >>
>> >>
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>