Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Selection by row

  1. #1
    Join Date
    30th November 2004
    Posts
    199

    Selection by row

    Hi

    Example at bottom....


    Is there anyway to do this by row until is comes to the empty row, the reason being is when I only have one row instead of many it End(xldown)into my other data...which then it is no good.

    I was just trying to get it to select by row until it comes to the blank row, instead of by region like it is...

    Anyhelp is greatly appreciated!

    Range(Selection, Selection.End(xlDown)).Select

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Selection by row

    Quote Originally Posted by tjolley
    Is there anyway to do this by row until is comes to the empty row, the reason being is when I only have one row instead of many it End(xldown)into my other data...which then it is no good.
    Sorry but this is very vague.

    Can you try to explain exactly what it is you are trying to do ?
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  3. #3
    Join Date
    5th August 2003
    Posts
    531

    Re: Selection by row

    Not sure if it will work but give this a try:

    VB:
    Selection.CurrentRegion.Select 
    
    
    Note - This may select rows above your selection, and will select all columns of data.
    If you only want the first column, you can adjust by:

    VB:
    selection.currentregion.Columns(1).Select 
    
    
    If you don't want rows above the current selection, we could tweak. Post back if this is the case.

    Hope this helps

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th November 2004
    Posts
    199

    Re: Selection by row

    Not vague at all! No Problem

    This line of Code works great...Selection.CurrentRegion.Select

    Ionly have one problem....I selected the first 36 columns and some columns are empty...and with the current line of code it selects the region upto the empty column...

    Is there a way to keep my 36 columns and then select the Region?

    Thank you all for your help too!

    tim

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    30th November 2004
    Posts
    199

    Re: Selection by row

    What about Union?

    I'm trying some things too...hmmm

    Thanks gang.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Selection by row

    Instead of XLDOWN, try using XLUP

    i.e from row 65536, define the last row as say

    VB:
    myrow = cells(65536,1).End(xlup).Row 
    
    
    This gets you around the issue of having the first blank row "on the way down" returned as your "last row" - if it's the column that is the issue, you could start with column IV and

    VB:
    mycol = cells(1,256).End(xltoleft).column 
    
    
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  7. #7
    Join Date
    30th November 2004
    Posts
    199

    Re: Selection by row

    Humm...thats sounds good...unfortunatly because I'm not very good at explaining things, that won't work in this particular delima


    Can we do the following example but -1?

    Range(Selection, Selection.End(xlDown)).Select


    Sorry to be such a pain!

    Tim

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Selection by row

    Quote Originally Posted by tjolley
    Humm...thats sounds good...unfortunatly because I'm not very good at explaining things, that won't work in this particular delima


    Can we do the following example but -1?

    Range(Selection, Selection.End(xlDown)).Select


    Sorry to be such a pain!

    Tim
    Well you said it!

    I really have no idea what your problem is given your attempts to explain it thus far. My last post was a "guess" at what the issue might be, but now you've totally lost me

    Any way you might be able to attach an example with an explanation of what is happening versus what you want to happen ideally..... remember that we can't see your issue unless you either explain it well or attach it...

    I'll be offline now for a bit but may chack back later.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  9. #9
    Join Date
    13th September 2003
    Location
    Singapore
    Posts
    1,236

    Re: Selection by row

    Quote Originally Posted by tjolley
    Can we do the following example but -1?

    Range(Selection, Selection.End(xlDown)).Select

    Tim
    my guess is you are trying to select to the last 2nd last non empty row ?
    ie select a range until the last row -1 row?
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

  10. #10
    Join Date
    5th August 2003
    Posts
    531

    Re: Selection by row

    Try this -

    VB:
    Dim RCount As Long 
    RCount = Selection.CurrentRegion.Rows.Count 
    Range(Selection.CurrentRegion, Selection.CurrentRegion.Cells(RCount, 1).Offset(0, 35)).Select 
    
    
    This grabs everything from the 1st cell in the current region to the last row, 36th column of the current region (or technically 35 columns from the beginning of the current region.

    This isn't perfect, as if you click on an empty cell, it will select that + 35 other cells in that row, but I think it might work for your problem. Post back with more questions.

    WillR - I think the confusion is that tjolley has additional data below this data set that is unrelated and he doesn't want selected. That is why the End(xlup) method won't work (it runs into this other data). That's why I used the current region approach instead.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 4
    Last Post: February 26th, 2013, 20:54
  2. Replies: 6
    Last Post: July 22nd, 2007, 14:52
  3. Replies: 9
    Last Post: March 23rd, 2007, 13:39
  4. Text box selection from a combo box selection
    By cie00011 in forum Excel and/or Access Help
    Replies: 1
    Last Post: May 2nd, 2006, 06:03
  5. Replies: 5
    Last Post: September 8th, 2003, 01:25

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
  •  
porno