Search code examples
excelvbscriptexcel-2013vba

select all rows for column except header in a seperate worksheet

I've tried various ways and answers to select all of the rows except the header for a certain column and none seem to work.

I've tried using (15 is the column here):

 Range(Cells(2, 15), Cells(.Cells(rows.Count, 15).End(xlUp).Row, 15)).Select

I managed to use .Activate on the worksheet with a different statement to select all, but this changes the sheet and you could visibly see all the rows being selected. This isn't possible for what I need it for. Users can't have a bunch of sheets constantly being switched in front of them, makes for a bad experience.

How can I select all of the non-blank columns after the header (first) row without using .Activate?

I need to get these values, put them in an array, and check if the current cell value is in the array. Not asking for this part, but providing it as context if it matters.

Solution

  • You can not select a range on a non-active worksheet.

    Here is how you can set a reference to all the cells in a column except the header row.

    Dim TargetRange As Range

With Worksheets("Sheet1")

    Set TargetRange = .Range(.Cells(2, 15), .Cells(Rows.Count, 15).End(xlUp))

End With