Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select multiple columns based on selected cells

Status
Not open for further replies.

rustified

Technical User
Jun 21, 2007
3
US
For this macro, I'm trying to get it to work so that once a user selects multiple cells (A1, C3, F7, etc), the macro selects 100 cells below that so the range is (A1:A100, C1:C100, F1:F100). It does this for however many cells are selected.
This range then needs to be saved for some other use, like plotting. (save it as a variable?)

Is there a simple way of doing this?

I'm thinking of a while loop, but not sure how to implement it for multiple selected cells.

I'm kinda new to VBA, so apologies if this is a too basic a question.

Thanks!
 
Have a look at the Areas property of the Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks! By using what you sand and a bunch of snippets of code I found online, I pieced this together. it doesn't quite work. Any suggestions?

(Code assums two things were selected for now)

Code:
Dim UpBound As Range
Dim LowBound As Range
Dim userSelections(1 To 10) As Range 'array that stores  the user selections
Dim allSelections As Range
Dim singlearea As Range

Dim icount As Integer 'counter

icount = 1
Set rangeToUse = Selection

    For Each singlearea In rangeToUse.Areas
        Set LowBound = singlearea
        Set UpBound = singlearea.End(xlDown)
        Set userSelections(icount) = Range(LowBound, UpBound)
    icount = icount + 1
    Next
       Set allSelections = Union(userSelections(1), _
                                 userSelections(2))
Range(allSelections).Select
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top