locked
Adding Cells to a Range RRS feed

  • Question

  • I have the following code.

    Private Sub Selector()
    Cells(1, 1).Select

    Selection.AddCell.cells(2,1) 'This should cause the VBA to select Cells A1 and A2

    Selection.EntireRow.Hidden = True 'This should hide the first two rows

    End Sub

    The second line of code obviously doens't work. Is there any way to add a cell to a selected range, without selecting the entire range in one line of code?

    Thank you.

    Tuesday, December 5, 2017 7:23 PM

All replies

  • You could use

        Cells(1, 1).Resize(2, 1).EntireRow.Hidden = False

    or, of course

        Range("A1:A2").EntireRow.Hidden = False

    Note that the code doesn't select any cells. If you want to start from the active cell, you could use

        ActiveCell.Resize(2, 1).EntireRow.Hidden = False


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 5, 2017 7:31 PM
  • Thank you, but I really need a way to add cells to a selected range that are *not* adjacent to each other.

    So perhaps...

    Select.cells(1,1)

    Selection.AddCell.Cells(5,1)

    Selection.EntireRow.Hidden = True

    ... was more of what I was looking for. Something that will hide nonadjacent rows. And it is not known which rows need to be hidden until the program is run.

    Tuesday, December 5, 2017 8:12 PM
  • One option would be to hide each row in turn as you decide which ones...

    Or:

        Dim rng As Range
        Set rng = Cells(1, 1)
        Set rng = Union(rng, Cells(5, 1))
        ...
        Set rng = Union(rng, Cells(37, 1))
        rng.EntireRow.Hidden = True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 5, 2017 10:08 PM