Announcement

Collapse
No announcement yet.

set last row of current region as variable

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Code:
    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

  • #2
    Re: set last row of current region as variable

    Try something like this:

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

    Comment


    • #3
      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:

      Code:
      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.

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment


          • #6
            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:

            Code:
            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).

            Comment


            • #7
              Re: set last row of current region as variable

              Instead of:

              =.Range("x, 1")

              ...try:

              =.Cells(x, 1)

              Comment


              • #8
                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!

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    Re: set last row of current region as variable

                    Thanks Greychild, I've attached it now, sorry didn't get back sooner!
                    Attached Files

                    Comment


                    • #11
                      Re: set last row of current region as variable

                      Replace all your code to this
                      Code:
                      Public n As Integer
                      Sub regionend()
                          Range("A1").Select
                          With ActiveCell.CurrentRegion
                              n = .Rows(.Rows.Count).Row
                          End With
                          Filter_defineranges
                      End Sub
                       
                      Sub Filter_defineranges()
                          ActiveWorkbook.Names.Add Name:="paste", RefersToR1C1:="=Sheet2!R1C1"
                           
                          With Sheet1.Range("a1")
                              .CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
                              CriteriaRange:=.Cells(n + 2, 1).CurrentRegion, Unique:=False
                              .CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Range("paste")
                          End With
                          Sheet1.ShowAllData
                      End Sub

                      Comment


                      • #12
                        Re: set last row of current region as variable

                        Awesome, thanks Chito. Would I be right in saying that the main thing that was wrong was .CurrentRegion was straight after .Range("A1") before and the correct syntax is to have it the line below?

                        In any case thank you!

                        Comment


                        • #13


                          Re: set last row of current region as variable

                          The obvious error is that your n variable should on top the sub not inside. Placing it inside will erase the data when you go out of the specific sub. Placing it on top will make it available to other sub. Using public will make it available for other modules while dim will make it available for sub located in the specific module.

                          Second, you name the range in sheet2 as paste so the destination should be paste.

                          Comment

                          Working...
                          X