View Full Version : Select multiple rows based on selected column
RcNorth
04-30-2010, 11:43 AM
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
04-30-2010, 11:57 AM
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
04-30-2010, 12:17 PM
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
04-30-2010, 12:24 PM
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
04-30-2010, 12:25 PM
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
04-30-2010, 12:29 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.