Excel Macro Special Cell Selection
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
Re: Excel Macro Special Cell Selection
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
Re: Excel Macro Special Cell Selection
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.
VB Code:
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
Re: Excel Macro Special Cell Selection
Or
VB Code:
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)
Re: Excel Macro Special Cell Selection
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