How to refer to a range using variable?

Question

I want to refer to a range of cells across columns: B:C then E:M (skipping D). I want to copy the cells and paste them to another worksheet.

I have a For Next loop with the row number variable iT. How do I select them using the variable?

This selects the whole range including D.

Sheet4.Range("B" & iT & ":C" & iT, "E" & iT & ":M" & iT).Select

I tried Cells().

Answer 1

If you want to use Cells Method.

Sub CopyUsingCellsMethod()
    Dim ColumnNumber As Long                                                                  
    Dim RowNumber As Long
    RowNumber = 1  'Enter Your Required Row Number Here

    With ThisWorkbook.Worksheets("Sheet4")
        For ColumnNumber = 2 To 5 Step 3  'This would Copy Range(B1:C1) into Range(I1:J1) and Then Range(E1:F1) into Range(L1:M1)
            .Range(Cells(RowNumber, ColumnNumber), Cells(RowNumber, ColumnNumber + 1)).Copy Worksheets("Sheet4").Range(.Cells(RowNumber, ColumnNumber + 7), .Cells(RowNumber, ColumnNumber + 8))
        Next ColumnNumber
    End With
End Sub

Answer 2

Try this

   Sht.range("A:M").copy AnotherWorkbook.sheets("YourSheet").range("A1")
   AnotherWorkbook.sheets("YourSheet").range("D:D").delete

Answer 3

A Brief Study

Copy Values, Formats, Formulas

Sub NonContiguousRow()
    Dim iT As Long
    iT = 1
    Dim cols As Range
    Set cols = Sheet1.Range("B:C,E:M")
    ' Optionally:
    'Set cols = Union(Sheet1.Columns("B:C"), Sheet1.Columns("E:M"))
    Dim rRng As Range
    Set rRng = Intersect(Sheet1.Rows(iT), cols)
    rRng.Copy Sheet2.Cells(1, "A")
    ' This will also work:
    'Dim ColumnsCount As Long
    'ColumnsCount = getColumnsCount(cols)
    'rRng.Copy Sheet2.Cells(1, "A").Resize(, ColumnsCount)
    ' This will NOT work:
    'Sheet2.Cells(1, "A").Resize(, ColumnsCount).Value = rRng.Value
End Sub

Function getColumnsCount( _
    aRange As Range) _
As Long
    If Not aRange Is Nothing Then
        Dim rng As Range
        For Each rng In aRange.Areas
            getColumnsCount = getColumnsCount + rng.Columns.Count
        Next rng
    End If
End Function

Copy Values

Sub TESTgetRow()
    Dim iT As Long
    iT = 1
    Dim cols As Range
    Set cols = Sheet1.Range("B:C,E:M")
    Dim Data As Variant
    Data = getRow(cols, iT)
    Sheet2.Cells(1, "A").Resize(, UBound(Data) - LBound(Data) + 1).Value = Data
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the values at the intersection of a range
'               and one of its worsheet's rows, in an array.
' Remarks:      Supports non-contiguous ranges.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function getRow( _
    aRange As Range, _
    Optional ByVal aRow As Long = 1) _
As Variant
    If Not aRange Is Nothing Then
        Dim rRng As Range
        Set rRng = Intersect(aRange, aRange.Worksheet.Rows(aRow))
        If Not rRng Is Nothing Then
            With CreateObject("Scripting.Dictionary")
                Dim rng As Range
                Dim cel As Range
                Dim n As Long
                For Each rng In rRng.Areas
                    For Each cel In rng.Cells
                        n = n + 1
                        .Item(n) = cel.Value
                    Next cel
                Next rng
                getRow = .Items
            End With
        Else
        ' Row range is empty ('Nothing').
        End If
    Else
        ' Range is empty ('Nothing').
    End If
End Function