CODEWITHSUNDEEP
excelvbarangeselection
Chris
Chris

Reputation: 35

Looking for an easier way to select a (part of) Column (not EntireColumn) within a Range worksheet.("upperleftmostPointByName").CurrentRegion

I'm looking for an easy way to select the columns within a given Range.

The Sub below does this, but it seems to be too complicated...

Somebody an idea?

Sub selectColumnInCurrentRegion(columnNrToBeSelected as Integer)
    
    Dim myRange  As Range
    Dim myArr As Variant 'just for debugging purposes
    
    Dim myWS As Worksheet
    Set myWS = ws_TargetForCGraphSearch 'Adjust your Worksheet
    
    Dim upperLeftCornerAsName As String
    upperLeftCornerAsName = "TargetResultStartPoint" 'Adjust your Corner
    
    Set myRange = Application.Intersect(myWS.Range(upperLeftCornerAsName).CurrentRegion, _                                                                
                  myWS.Range(upperLeftCornerAsName).Offset(0, columnNrToBeSelected -1).EntireColumn)
    myArr = myRange
    
    myRange.Select

 End Sub

Thank you in advance!

Upvotes: 0

Views: 39

Answers (1)

QHarr
QHarr

Reputation: 84465

Use the columns property to access column of choice from CurrentRegion range object.

Set myRange = myWS.Range(upperLeftCornerAsName).CurrentRegion.Columns(columnNrToBeSelected)

Belts and braces, you would check myWS.Range(upperLeftCornerAsName).CurrentRegion is valid and the columnNrToBeSelected is <= .columns.Count of .CurrentRegion and >0.

Upvotes: 1

Related Questions