| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select multiple ranges in Excel with vbs
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(Selectio nPart1_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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
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" wrote in message ... 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(Selectio nPart1_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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
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" skrev i en meddelelse ... 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(Selectio nPart1_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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
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(Selectio nPart2_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" wrote in message ... 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" skrev i en meddelelse ... 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(Selectio nPart1_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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
No it is not possible, but as I said, you usually don't need to select.
-- Regards, Tom Ogilvy "Stefan Mueller" wrote in message ... 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(Selectio nPart2_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" wrote in message ... 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" skrev i en meddelelse ... 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(Selectio nPart1_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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
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" wrote in message ... No it is not possible, but as I said, you usually don't need to select. -- Regards, Tom Ogilvy "Stefan Mueller" wrote in message ... 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(Selectio nPart2_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" wrote in message ... 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" skrev i en meddelelse ... 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(Selectio nPart1_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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
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" wrote in message ... 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" wrote in message ... No it is not possible, but as I said, you usually don't need to select. -- Regards, Tom Ogilvy "Stefan Mueller" wrote in message ... 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(Selectio nPart2_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" wrote in message ... 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" skrev i en meddelelse ... 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(Selectio nPart1_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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
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 "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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" wrote in message ... No it is not possible, but as I said, you usually don't need to select. -- Regards, Tom Ogilvy "Stefan Mueller" wrote in message ... 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(Selectio nPart2_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" wrote in message ... 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" skrev i en meddelelse ... 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(Selectio nPart1_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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select multiple ranges in Excel with vbs
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" wrote in message ... No it is not possible, but as I said, you usually don't need to select. -- Regards, Tom Ogilvy "Stefan Mueller" wrote in message ... 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(Selectio nPart2_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" wrote in message ... 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" skrev i en meddelelse ... 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(Selectio nPart1_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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| select multiple ranges in formula | Excel Discussion (Misc queries) | |||
| in charting, how do i select data ranges from multiple sheets, sa. | Charts and Charting in Excel | |||
| select multiple cell ranges in "sumif" formula? | Excel Worksheet Functions | |||
| Select instersection of two ranges | Excel Programming | |||
| VBA-Select several ranges using variables | Excel Programming | |||
Linear Mode
