+ Reply to Thread
Results 1 to 14 of 14

Select a Row rather than just the Cell in the Row

  1. #1
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Select a Row rather than just the Cell in the Row

    Hello

    Using the following code, how can I change it so it will select the actual row (hence highlighting it) rather than just selecting the cell that is in the row?
    I assume I could use the Rows("xx:xx").Select method, but I'm not sure how to make it work in the following code:

    Basically, I'm just trying to select the row that has the current date in it. Currently, it only selects the cell that has the date rather than the entire row.

    Please Login or Register  to view this content.

    Thank you!
    Last edited by Modify_inc; 09-15-2018 at 04:02 PM. Reason: Explanation

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Select a Row rather than just the Cell in the Row

    try
    Please Login or Register  to view this content.

  3. #3
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Select a Row rather than just the Cell in the Row

    These statments are equivalent for rows:
    Please Login or Register  to view this content.
    Same for Columns
    Please Login or Register  to view this content.
    But if you can learn to use code without the 'Select' in it, the code will be much more efficient. That takes some time and studying to develop, but well worthwhile.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select a Row rather than just the Cell in the Row

    Maybe, untested:
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Select a Row rather than just the Cell in the Row

    Quote Originally Posted by TMS View Post
    Maybe, untested:
    Please Login or Register  to view this content.
    Or
    Please Login or Register  to view this content.
    and drop the Application.GoTo

  6. #6
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Select a Row rather than just the Cell in the Row

    Thanks for all the quick suggestions! I will test them shortly...thanks again!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select a Row rather than just the Cell in the Row

    I think the Application.GoTo will scroll to the row.

  8. #8
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Select a Row rather than just the Cell in the Row

    These both worked:

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    Thank you!

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Select a Row rather than just the Cell in the Row

    Glad we could help & thanks for the feedback

  10. #10
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Select a Row rather than just the Cell in the Row

    Quote Originally Posted by JLGWhiz View Post
    Or
    Please Login or Register  to view this content.
    and drop the Application.GoTo
    Nevermind, it did work! I forgot to remove the Boolean from the end. It actually works better than the others, because it keeps the row more centered when selected, rather than bringing it all the way to the top.
    Last edited by Modify_inc; 09-16-2018 at 01:24 PM. Reason: It did work..sorry!

  11. #11
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Select a Row rather than just the Cell in the Row

    I know I just marked this as Solved, but I just noticed a small issue. I'm not sure what has changed, but now when I open the workbook, and the main sheet loads by default, which has the above code in it, it does nothing! I have to select another sheet, then come back to this one, and it works fine. Why would the main sheet not be considered activated when it is the one that Excel opens to when I open the workbook? It was working yesterday; I even tried using my original code and still the same thing.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Select a Row rather than just the Cell in the Row

    An Activate Event only triggers when you select(activate) that sheet. If the sheet is already active when the workbook opens then the Event won't trigger.
    You can put your code in a workbook_open event, but you will need to specify what sheet to run on, incase another sheet is active.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Select a Row rather than just the Cell in the Row

    You're welcome. Thanks for the rep.

    With regards to the GoTo or Select, I guess it depends how many rows of data there are and whether or not the row that is found is within the visible window.

  14. #14
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Select a Row rather than just the Cell in the Row

    Quote Originally Posted by Fluff13 View Post
    An Activate Event only triggers when you select(activate) that sheet. If the sheet is already active when the workbook opens then the Event won't trigger.
    You can put your code in a workbook_open event, but you will need to specify what sheet to run on, incase another sheet is active.
    Thank you, I got it working now!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Find Matching text in a cell and select a specif cell
    By Carolynsdesk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2013, 07:28 AM
  2. Replies: 4
    Last Post: 06-17-2013, 05:21 AM
  3. VBA select cell range based upon the # of rows that make up a certain merged cell?
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-04-2012, 12:22 PM
  4. Find specific value in a cell, select the cell and copy all cells beneath
    By Mothman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2010, 03:26 AM
  5. Replies: 2
    Last Post: 12-09-2008, 12:44 AM
  6. Select Data from a cell, move down a number, select data
    By Kage_ in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-12-2007, 02:12 PM
  7. Replies: 6
    Last Post: 06-30-2006, 01:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1