Excel Macro Special Cell Selection

[mchanguion]

Your help here would be great .....

I am trying to use an excel macro to select 2 ranges on the same sheet:
- the first range is A1:G56
- the second range is I59 to the last active cell. This last active cell is always in column I and is always at least 10 rows greater than row 59.
The problem is that the macro that excel generates (below) always selects A1:I(x) where x is the last row number. eg if the last active cell in column I is 90, A1:I90 is selected. The ranges I do not need selected are A57:G90 and I1:I55.

Range("A1:G56,I59").Select
Range("I59").Activate
Range(Selection, ActiveCell.SpecialCells(xlCellTypeLastCell)).Select
Range(Selection, Selection.End(xlUp)).Select

Would really appreciate your help on this one.
Cheers
Marc

[Ecniv]

Hmm had a go. Weirdly doesn't like me make multiple range seklections using the range command :/

Guess it only works if you specify via address type (such as the recording you made already).

sorry couldn't help

[DKenny]

You cna use the Union Method to combine multiple ranges in excel. Here's some code that will union the ranges that you have described.

Sub MultiRangeSelect()
Dim rngTopSection As Range
Dim rngBottomStart As Range
Dim rngBottomSection As Range
Dim rngCombined As Range
    
    Set rngTopSection = ThisWorkbook.Worksheets(1).Range("A1:G56")
    
    Set rngBottomStart = ThisWorkbook.Worksheets(1).Range("I59")
    Set rngBottomSection = Range(rngBottomStart, rngBottomStart.End(xlDown))
    
    Set rngCombined = Application.Union(rngTopSection, rngBottomSection)
    
    rngCombined.Select
    
End Sub

[bilm_ks]

Or

Sub SelectRange()
 
Dim r1 As Range, r2 As Range
 
Set r1 = ActiveSheet.Range("A1:G56")
Set r2 = ActiveSheet.Range("I59", Cells.SpecialCells(xlCellTypeLastCell))
ActiveSheet.Range(r1.Address & "," & r2.Address).Select
 
End Sub

BUT DKenny's solution is better (doesnt involve string concat operations)

[mchanguion]

It works perfectly both ways - thank you both very much for your help - this has helped me put the finishing touches to a pricing system I have developed for work.
Cheers
Marc