How to select multiple ranges in Excel with vbs

[Stefan Mueller]

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]

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

 

[Tom Ogilvy]

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

 

[Leo Heuser]

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

 

[Stefan Mueller]

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

 

[Tom Ogilvy]

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

 

[Stefan Mueller]

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]

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.aspx?scid=kb;en-us;184273
ACC97: How to Use Automation to Create a Microsoft Excel Chart

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;157940
XL97: How To Create a Dynamic Chart Using Visual Basic

http://support.microsoft.com/default.aspx?scid=kb;en-us;109575
XL: Cannot Use Array as Source Argument with SeriesCollection

http://support.microsoft.com/default.aspx?scid=kb;en-us;213653
XL2000: Cannot Use Array as Source Argument with SeriesCollection

http://support.microsoft.com/default.aspx?scid=kb;en-us;172114
XL: Cannot Use Array of Data Points with Extend Method

http://support.microsoft.com/default.aspx?scid=kb;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.aspx?scid=kb;en-us;186219
XL97: Excel Quits Unexpectedly Running Macro That Creates Chart

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;137016
XL: Macro to Extract Data from a Chart

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

http://support.microsoft.com/default.aspx?scid=kb;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.aspx?scid=kb;en-us;139662
XL: How to Use a Visual Basic Macro to Create a Bubble Chart

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;213814
XL2000: Macro to Extract Data from a Chart

http://support.microsoft.com/default.aspx?scid=kb;en-us;161858
XL97: How to Trap Events for an Embedded Chart

 

[Stefan Mueller]

Hello Tom

First of all I have to correct you because I've studied your code about the
union without selecting very carefully. And I really appreciate it very much
that you have even pasted some code. This is for a beginner like me really
very helpful. Many thanks.

But I couldn't think about a solution how to use this for drawing a chart.
I've already done quite a lot of seaching in the internet about a solution
to draw a chart on one sheet and getting the data from another sheet.
Unfortunately without success. But with the command union you gave me a
solution to draw my charts.
I'm really not sure if it's possible to draw a chart without selection the
range on another sheet. But of course I'll have a look to all the links
you've posted.

Many thanks again
Stefan

 

[Stefan Mueller]

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