Selecting Entire Row by Clicking a Cell

[madhuchelliah]

Hello Guys, I want to select entire row if i click on the any of the cell in that row. Is this possible?

 

[PatOBrien198]

Here's one way to do it...

Create a named range for those cells where you want the action to accur..... say a name like "SelectEntireRow"

then add the following code to the "ThisWorkbook" object:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Range("SelectEntireRow")) Is Nothing Then
        Target.EntireRow.Select
    End If
End Sub

 

[madhuchelliah]

Hi Pat. i appreciate your response. Since i am a beginner could you please explain in details.

 

[PatOBrien198]

There are multiple ways to create a named range. One way is to select the range then type the name you want to create in the address box, then hit return.

Another way is to click the "Formulas" tab, then select "Name Manager" and click "New", enter a descriptive name, and select the range you it to apply to in the "Refers to:" text box, click OK.

Then start up the VBA editor (one way is to type Alt+F11 keys), then select "ThisWorkbook" in the list of Objects that are listed for the workbook that you are working in. In the code window (ususally on the right side) paste the following code (note: I added an error trapping statement):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo ErrorHandler
    If Not Intersect(Target, Range("SelectEntireRow")) Is Nothing Then
        Target.EntireRow.Select
    End If
ErrorHandler:
End Sub

You will have to save the workbook as a macro enabled workbook (with file extension .xlsm).

What the macro does is each time a cell is selected, the "Worbook_SheetSelectionChange" event is fired, it checks to see if the selected cell is within (intersects with) the named range you created earlier. If it is, then the entire row is selected.

 

[madhuchelliah]

Thanks Pat, Working Great!!!!!!

 

[madhuchelliah]

There are multiple ways to create a named range. One way is to select the range then type the name you want to create in the address box, then hit return.

Another way is to click the "Formulas" tab, then select "Name Manager" and click "New", enter a descriptive name, and select the range you it to apply to in the "Refers to:" text box, click OK.

Then start up the VBA editor (one way is to type Alt+F11 keys), then select "ThisWorkbook" in the list of Objects that are listed for the workbook that you are working in. In the code window (ususally on the right side) paste the following code (note: I added an error trapping statement):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo ErrorHandler
    If Not Intersect(Target, Range("SelectEntireRow")) Is Nothing Then
        Target.EntireRow.Select
    End If
ErrorHandler:
End Sub

You will have to save the workbook as a macro enabled workbook (with file extension .xlsm).

What the macro does is each time a cell is selected, the "Worbook_SheetSelectionChange" event is fired, it checks to see if the selected cell is within (intersects with) the named range you created earlier. If it is, then the entire row is selected.

Hello Pat, Is there any possibility to select some range of cells in instead of selecting entire row?

 

[PatOBrien198]

Instead of the Target.EntireRow.Select statement use:

range(cells(target.Row, 2),cells(target.Row, 5)).select

change the 2 and the 5 to correspond with the start column and thru column that you want to select.

 

[madhuchelliah]

Thank you Pat !!!!!