Automatically Select Entire Worksheet Row on Click inside any cell Using Excel VBA

Home More Excel (VBA) Training Videos

I watched your video about VBA control button. I must say your way of teaching is really good. I need your help about some

Excel work. If I  select a  random cell from sheet and I want to select entire row of selected cell, is this possible? Please help!



Implementing the selection of an entire row when you click in any cell of that row using VBA:

We'll create a command button and attach the code to this command button. Clicking on a command button and experiencing the VBA code in action is intuitive.
  • Click on the Developer tab
  • Click on insert
  • From the 'Activex Controls' we select the command button icon
  • On our worksheet Sheet1 we click and drag to draw and place a command button with the caption CommandButton1
  • In the Controls group click on the properties icon while the command button is kept selected
  • In the Controls Properties window selct the caption CommanButton1 in the text box right to the item 'Caption' and over-write it with 'Select Entire Row' and press enter
  • Next close the properties window
  • Double-click on the command button which now has the caption 'Select Entire Row'
  • Now the Visual Basic for Applications windows opens up with the following two lines of code: 
  •  Private Sub CommandButton1_Click()  End Sub
  • Between these two lines of code we write a line of code  and our macro looks as shown below
Private Sub CommandButton1_Click()
'The active cell is the cell inside which the user clicks
End Sub

  • Next we sava the file as 'Excel-Macro-Enabled Workbook' with the file extension '.xlsm as compared to the normal Excel file with the file extension '.xlsx' in Excel 2007
  • We come back to our worksheet and click on the 'Design Mode' in the Controls group of the Developer tab to turn off the design mode
  • Select cell A4
  • Click on the command utton and the complete row 4 gets selected
  • Click on cell B6 and then click on the command button and the complete row 6 gets selected

It is also possible to select the entire row automatically without Excel VBA.
  • Select the cell
  • Click inside the name box
  • Assign the name 'r' to the cell 
  • Press enter and the whole row in the worksheet is highlighted
Watch the video below to see how the automatic selection of an entire worksheet is implemented quickly and easily when you click inside any cell:

Home More Excel (VBA) Training Videos