CODEWITHSUNDEEP
vbarangedynamic-columns
horace_vr
horace_vr

Reputation: 3176

Excel VBA set range as multiple columns

I am currently trying to assign multiple columns to a range. I need to reference the columns using worksheet name and column numbers.

dim MyRange as Range
dim StartCol as Integer
set MyRange = [from column StartCol until Startcol+5]

Any input would be much appreciated...

Upvotes: 0

Views: 8376

Answers (2)

Harry S
Harry S

Reputation: 511

'
' get the columns of range Rr
' Cols as CSV like   2,4,7   b,d,G   B,D:F,m,P  3,H,J,2
' 3,4,5,6 is C:F   6,8:11,3 is bad => 6,8,3   B,D:F  is 2,4,5,6
'
Function GetRaColl(Rr As Range, ColS$) As Range
   Dim SA$(), LI&, VV&, UB&, URa As Range
   SA = Split(ColS, ",")
   UB = UBound(SA)
   VV = Val(SA(0))
   If VV > 0 Then
      Set URa = Rr.Columns(VV)
   Else
      Set URa = Rr.Columns(SA(LI))
   End If
   ' if more ranges
  For LI = 1 To UBound(SA)
      VV = Val(SA(LI))
      If VV > 0 Then   ' so treats 3:5 as 3  so use C:E or 3,4,5
         Set URa = Union(URa, Rr.Columns(VV))
      Else
         Set URa = Union(URa, Rr.Columns(SA(LI)))
      End If
Next LI
Set GetRaColl = URa
End Function

Upvotes: 0

luke_t
luke_t

Reputation: 2985

Option Explicit
Sub col()
    Dim rngCols As Range, ws As Worksheet, wb As Workbook
    Dim startCol As Integer

    set wb = ThisWorkbook
    set ws = wb.Sheets("MySheet")
    startCol = 1
    With ws
        Set rngCols = .Range(.Cells(1, startCol), .Cells(.Rows.Count, startCol + 5))
    End With
    rngCols.Select
End Sub

This should do what you're after. You will just need to amend the startCol variable, the worksheet variable, and the integer you're adding to the startCol.

Upvotes: 1

Related Questions