none
How do you add a cell to a range RRS feed

  • Question

  • Feel like an idiot for asking this, but how on earth do you add a cell to a range?

    Ive declared range

    I want to add a particular cell to that range?

    How?


    Sub AddtoRange()
    
    Dim HighRange As Range
    Dim LowRange As Range
    Dim WholeRng As Range
    Dim i, j As Integer
    
    i = 1 '1 down
    j = 2 'column 2 ("B")
    
    
    
    Set WholeRng = Range("B1:B26")
    
    For Each Cell In WholeRng
        If Cell.Value = "High" Then
        'use negative y to offset left
            Cells(i, j).Offset(0, -1) = "1 left"
        ' add cell to HighRange in other words add A1 to High Range if B1 has the value "high"
        
        ElseIf Cell.Value = "Low" Then
            Cells(i, j).Offset(0, -1) = "1 left"
            
        ' add cell to LowRange
        End If
        
    
    
    End Sub


    • Edited by VBNovice01 Friday, January 20, 2017 10:22 AM
    Friday, January 20, 2017 10:14 AM

All replies

  •     Dim rng As Range

        Set rng = Range("A1:A20")

        Set rng = Union(rng, Range("A21"))

        MsgBox rng.Address

    Note that the added cell or range can be any cell or range; not necessarily an adjacent cell or range.        


    Regards, OssieMac

    Friday, January 20, 2017 10:22 AM
  • how can i make it so that it is the adjacent cell or the one I want?
    Friday, January 20, 2017 10:23 AM
  • Appears that you were editing your original post while I was answering.

    I am not sure exactly what you are attempting to do but the Union function allows multiple ranges to be assigned to one range variable.

    There is a caveat. You need to ensure that the range variable actually has an initial range assigned to it before the Union function can be used.

        Dim rng As Range
       
        Set rng = Range("A1:A20")
       
        'If not nothing then already has a range assigned
        'so use Union to add a range to it
        If Not rng Is Nothing Then
            Set rng = Union(rng, Range("A21"))
        Else
            'If is nothing then no existing range assigned
            'so cannot use union so just assign the range on its own
            Set rng = Range("A21")
        End If 


    Regards, OssieMac

    Friday, January 20, 2017 10:36 AM
  • Additional info that might help

    If I am interpreting your code correctly then like the following (but untested)

         If cell.Value = "High" Then
            'use negative y to offset left
            If Not HighRange Is Nothing Then
                Set HighRange = Union(cell, Cells(i, j).Offset(0, -1))
               
            Else
                'If is nothing then no existing range assigned
                'so cannot use union so just assign the range on its own
                Set HighRange = Cells(i, j).Offset(0, -1)
            End If
        End If


    Regards, OssieMac

    Friday, January 20, 2017 10:45 AM
  • if I want to copy HighRange to Sheet 2 to ensure Ive got the correct outcome

    im trying this

    Worksheets("Sheet2").Range("A1").Value = HighRange.Value

    Friday, January 20, 2017 11:32 AM
  • You can copy a split range if the split range creates a regular shape of rows by columns but if it creates a staggered range then it will not copy and paste.

    Example:

    The following will copy and paste because it is 2 regular shapes that can be put together to create one regular shape but it does not paste to the corresponding columns of A and D; it pastes to columns A and B as a contiguous range.

    Set HighRange = Range("A1:A4")
    Set HighRange = Union(HighRange, Range("D1:D4"))
    HighRange.Copy Destination:=Worksheets("Sheet2").Range("A1")

    The following will not copy and paste because the union is a staggered range but the union range is quite valid and you can iterate through all of the cells in the union.

    Set HighRange = Range("A1:A4")
    Set HighRange = Union(HighRange, Range("D4:D8"))
    HighRange.Copy Destination:=Worksheets("Sheet2").Range("A1")

    Iterate through the range as per the following example. The MsgBox displays the address but you can also retrieve the value (using cel.Value) in lieu of the address.

    Dim HighRange
    Dim cel As Range
    Set HighRange = Range("A1:A4")
    Set HighRange = Union(HighRange, Range("D4:D8"))
    For Each cel In HighRange
        MsgBox cel.Address
    Next cel


    Regards, OssieMac

    Friday, January 20, 2017 7:41 PM