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 13

Thread: set last row of current region as variable

  1. #1
    Join Date
    21st December 2004
    Posts
    51

    set last row of current region as variable

    Hi all

    I have got the following code, I am trying to firstly set the last row's number of the current region as a variable n. At the moment I have no idea how to do this as is probably apparent below!

    Then I need to reference the row number n+2 in column 1 for that is where advanced filter criteria will appear, but I don't think it's working just writing n+2... Any help would be much appreciated!

    VB:
    Sub regionend() 
        Range("A1").Select 
        ActiveCell.CurrentRegion.Select 
        n = ActiveCell.Row 'this sets n to 1 but I need to set it
         'to row number of last row in current region
        Filter_defineranges 
    End Sub 
     
    Sub Filter_defineranges() 
        ActiveWorkbook.Names.Add Name:="paste", RefersToR1C1:="=Sheet2!R1C1" 
         
        With Sheet1 
            .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace,  _ 
            CriteriaRange:=.Range( _"n+2,1").CurrentRegion, Unique:=False _ 
             'the above "n+2, 1" reference to a cell doesn't seem to be correct either,
             'so I need to reference this correctly also!
            .Range("Data").SpecialCells(xlCellTypeVisible).Copy Destination:="PasteRange" 
            .ShowAllData 
        End With 
    End Sub 
    
    
    Thanks a million to all the legends who have helped me before!

    Alek

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Greychild Guest

    Re: set last row of current region as variable

    Try something like this:

    VB:
    Sub Set_n() 
         
        Dim n As Long 
         
        With Range("A1").CurrentRegion 
            n = .Rows(.Rows.Count).Row 
        End With 
         
        MsgBox n 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: set last row of current region as variable

    If you are after the last row of the current region that the active cell is in, then also try:

    VB:
    Sub test() 
        Myrow = ActiveCell.CurrentRegion.End(xlDown).Row 
        MsgBox Myrow 
    End Sub 
    
    
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  4. #4
    Greychild Guest

    Re: set last row of current region as variable

    I'm not sure I agree with the above. If the active cell column has blanks in, the above code won't necessarily return the last row of the active cell's current region.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: set last row of current region as variable

    Agreed. If its an empty cell within the region or one that is one the edge of an area empty to the bottom of the region i will work, but if in a "middle" cell of an area contiguously empty to the bottom of the region it will return an erronious value. What I was unsure of is whether the current region would always contain "A1". My formulation gave more flexibility by using the ActiveCell as the starting point. But in hindsight, it looks like it would be better to mod your code to something like (don't know if I've got the syntax correct):

    [code]
    Sub Set_n()

    Dim n As Long

    With ActiveCell.CurrentRegion
    n = .Rows(.Rows.Count).Row
    End With

    MsgBox n

    End Sub
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  6. #6
    Join Date
    21st December 2004
    Posts
    51

    Re: set last row of current region as variable

    Thanks Tom and Greychild. It should always be A1 as current region so both work great I wander if I might pick your brains on the other bit though, after getting N I'm adding 2 to get a variable X to do the following:

    VB:
    With Sheet1 
        .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace,  _ 
        CriteriaRange:=.Range("x, 1").CurrentRegion, Unique:=False 
         'above I am supposed to be referring to row x 1st column by ("x, 1")
        .Range("A1").SpecialCells(xlCellTypeVisible).Copy Destination:="paste" 
        .ShowAllData 
    End With 
    
    
    At the moment if I take out the "."s from ".Range" the advanced filter works with references like A1 or A178 but but not with "x, 1" (which should refer to A178 as N was equal to 176 and then added 2) so I am not sure how to reference this? (I have criteria in the place that is being referenced already).

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Greychild Guest

    Re: set last row of current region as variable

    Instead of:

    =.Range("x, 1")

    ...try:

    =.Cells(x, 1)

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    21st December 2004
    Posts
    51

    Re: set last row of current region as variable

    Ta, I've tried this and now no longer get run time error 13 just run time error 4??? so a step in the right direction, am I referencing the range A1 current region correctly perhaps? Tried playing around but not getting anywhere fast!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Greychild Guest

    Re: set last row of current region as variable

    Do you have a small sample workbook you can post? I'm sure it's not hard to fix - I just need to have a play with a test file.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    21st December 2004
    Posts
    51

    Re: set last row of current region as variable

    Thanks Greychild, I've attached it now, sorry didn't get back sooner!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    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. Shortcut Key To Select Current Region
    By googlebot in forum EXCEL HELP
    Replies: 8
    Last Post: August 9th, 2008, 09:23
  2. Re: Macro: Name Current Region or CurrentRegion
    By SamMarino in forum Excel and/or Word Help
    Replies: 1
    Last Post: October 29th, 2006, 15:26
  3. Macro: Name Current Region or CurrentRegion
    By SamMarino in forum EXCEL HELP
    Replies: 5
    Last Post: October 29th, 2006, 10:13
  4. chart data region as variable
    By matty_v in forum EXCEL HELP
    Replies: 8
    Last Post: February 3rd, 2005, 18:14
  5. [Solved] Counting n. of rows in Current Region
    By Balangan in forum EXCEL HELP
    Replies: 2
    Last Post: June 18th, 2003, 00:23

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