Select multiple rows based on selected column

[RcNorth]

I have a spreadsheet that shows a grouping of data. Each column represents a message transaction. Each row represents a specific segment. A message can contain one or many segments. What I would like to do is that when an entire column is selected each row that has a value for that column to also be selected. This will highlight what segments are used in that particular message.

ie.

A B C D
1 X X X
2 X
3 X X

If Column B is selected Rows 1,2,3 would also be selected. If column D were selected row 1 would be selected. This would only happen if the entire column was selected. If an individual cell is selected then nothing else would be selected.

Thanks

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim mCol As Range
Dim LastRow As Long
Dim i As Long

On Error GoTo ws_exit
Application.EnableEvents = False

For Each mCol In Target.Columns

LastRow = Me.Cells(Me.Rows.Count, mCol.Column).End(xlUp).Row
For i = 1 To LastRow

If Me.Cells(i, mCol.Column).Value2 <> "" Then

If rng Is Nothing Then

Set rng = Me.Cells(i, mCol.Column).EntireRow
Else

Set rng = Union(rng, Me.Cells(i, mCol.Column).EntireRow)
End If
End If
Next i
Next mCol

If Not rng Is Nothing Then rng.Select
Target.Cells(1, 1).Activate

ws_exit:
Application.EnableEvents = True

End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[mbarron]

The following is triggered by a Selection Change and needs to be place in the code module for the appropriate sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, cCell As Range
Dim lRow As Long
Application.EnableEvents = False
If Target.Rows.Count = Rows.Count Then
lRow = Cells(Rows.Count, Target.Column).End(xlUp).Row

For Each cCell In Target
If cCell.Row > lRow Or lRow = 1 Then Exit For
If cCell.Row = 1 Then
Else
If cCell <> "" Then
If rng Is Nothing Then
Set rng = cCell.EntireRow
Else
Set rng = Union(rng, cCell.EntireRow)
End If
End If
End If
Next

End If

If Not rng Is Nothing Then
rng.Select
End If
Application.EnableEvents = True
End Sub

[mbarron]

This one will keep the Column highlighted as well
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, cCell As Range
Dim lRow As Long

If Target.Rows.Count = Rows.Count Then
lRow = Cells(Rows.Count, Target.Column).End(xlUp).Row

For Each cCell In Target
If cCell.Row > lRow Or lRow = 1 Then Exit For
If cCell.Row = 1 Then
Else
If cCell <> "" Then
If rng Is Nothing Then
Set rng = cCell.EntireRow
Else
Set rng = Union(rng, cCell.EntireRow)
End If
End If
End If
Next

End If

If Not rng Is Nothing Then
Set rng = Union(rng, Target)
rng.Select
End If
End Sub

[RcNorth]

Thanks guys the both come very close to the needed solution. mbarron is a bit better as it allows the selection of a single cell or row without selecting everything. Where as xld's solution will highlight the spreadsheet whenever a cell that has a value is selected.

One thing that I need to figure out to add to mbarron's solution is how to select the chosen column as well as all the rows. Currently the selected column is unselected so you don't know which message the selected segments are for.

[RcNorth]

Thanks mbarron the last one is exactly what I am looking for. I guess you were replying while I was typing up my last comment.

Consider this closed.