+ Reply to Thread
Results 1 to 10 of 10

Macro that will select range based off cell value in multiple rows

  1. #1
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Macro that will select range based off cell value in multiple rows

    I have a worker book that has data ranging from D4:AM103

    I'd like to be able to create a Macro that will look at column I4:I103 If any of these cells have data (last name) then it will select that entire row (from D:AM) Nothing more than SELECT is needed.

    The data is normally sorted first, so the rows that have data in them will likely be continuous...

    Sample attached. In this sample the macro would select range D4:AM15 as cells 4:15 have data in column I

    Thanks.

    Rob

    2014 CPS ONTARIO Sealing STATS.xlsx

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,840

    Re: Macro that will select range based off cell value in multiple rows

    Use Conditional Formatting. Select the entire range and use the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Macro that will select range based off cell value in multiple rows

    Thanks Proton - Won't that only highlight the rows though? I want to be able to select the rows (quickly) so that I can then copy/paste into another document..

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: Macro that will select range based off cell value in multiple rows

    Hi Rob,
    try
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Macro that will select range based off cell value in multiple rows

    That seems to work great Nilem, thanks! What if I wanted to copy the selection also? I just realized that would make even more sense...

    Thanks again..

    Rob

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: Macro that will select range based off cell value in multiple rows

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Macro that will select range based off cell value in multiple rows

    haha... You can see I'm not a pro at this!

    Thanks for all your help... I'll double check and play with it for a day and then mark as solved..

    Rob

  8. #8
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Macro that will select range based off cell value in multiple rows

    Hi Nilam - Works great Thanks. Marking as solved. Quick question. How would I change the macro to look at another column instead of I?

  9. #9
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: Macro that will select range based off cell value in multiple rows

    Please Login or Register  to view this content.
    6th column relative to the range D:AM, ie column I
    3d column is the column F
    10th is the column M, etc.

  10. #10
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: Macro that will select range based off cell value in multiple rows

    Perfect.

    Thanks again.

+ 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. [SOLVED] Macro to hide rows based on a range of data in a cell
    By eorydc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2013, 09:59 AM
  2. [SOLVED] Macro to copy rows (in a range) based on a cell criteria
    By Taislin in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-23-2013, 07:54 PM
  3. [SOLVED] Select rows based on date range and copy to multiple tabs
    By B-Rell in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-01-2012, 09:27 AM
  4. 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
  5. Macro to select range based on cell not being blank
    By neb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2005, 01:04 PM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.31456 seconds
  • Memory Usage 9,129KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (3)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (10)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (10)postbit_legacy
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1