Excel VBA Highlighting/selecting an unknown range of cells

[daniB]

Hiya Everyone!

I maybe going about this in a very convoluted way.......

Im trying to highlight/select a variable range of cells....So far I
have this code which highlights one column depending on the length of
the column (data filled). but I need to select it in variable number
of rows

Sub SelectColumn()

Dim startCell As Range, cell1 As Range, cell2 As Range
Dim rowNr&, colNr&

Set startCell = ActiveCell
rowNr = startCell.Row: colNr = startCell.Column

If IsEmpty(startCell) Then Exit Sub

For rowNr = startCell.Column To 1 Step -1
If IsEmpty(Cells(rowNr, colNr).Value) Then
Set cell1 = Cells(rowNr + 1, colNr)
Exit For
End If
Next rowNr

If cell1 Is Nothing Then Set cell1 = Cells(1, colNr)

For rowNr = startCell.Row To 500
If IsEmpty(Cells(rowNr, colNr).Value) Then
Set cell2 = Cells(rowNr - 1, colNr)
Exit For
End If
Next rowNr
If cell2 Is Nothing Then Set cell2 = Cells(500, colNr)

Range(cell1, cell2).Select

End Sub

I think it is probably staring me in the face...or there is a really
quick way of doing this...Can anyone help??

Thanks!

 

[Bernie Deitrick]

dani,

I think the sub below does the same thing, only quicker.

Depending on how your data is set up, a single line macro might work:

Sub SelectColumn3()
Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).Select
End Sub

HTH,
Bernie
MS Excel MVP

Sub SelectColumn2()
Dim Cell1 As Range
Dim Cell2 As Range
If IsEmpty(ActiveCell) Then Exit Sub
Set Cell1 = ActiveCell
Set Cell2 = ActiveCell
If Not IsEmpty(ActiveCell(0)) Then Set Cell1 = ActiveCell.End(xlUp)
If Not IsEmpty(ActiveCell(2)) Then Set Cell2 = ActiveCell.End(xlDown)
Range(Cell1, Cell2).Select
End Sub

 

[daniB]

Thank you thats done it great