Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Selection by row

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

  • 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

  • #2
    Re: Selection by row

    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

    LinkedIn: Will Riley

    Comment


    • #3
      Re: Selection by row

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

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

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

      Comment


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

        Comment


        • #5
          Re: Selection by row

          What about Union?

          I'm trying some things too...hmmm

          Thanks gang.

          Comment


          • #6
            Re: Selection by row

            Instead of XLDOWN, try using XLUP

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

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

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

            LinkedIn: Will Riley

            Comment


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

              Comment


              • #8
                Re: Selection by row

                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

                LinkedIn: Will Riley

                Comment


                • #9
                  Re: Selection by row

                  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

                  Comment


                  • #10
                    Re: Selection by row

                    Try this -

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

                    Comment


                    • #11
                      Re: Selection by row

                      Sorry WillR!

                      xlite and WillR...My sincere apologies for any confustion...

                      Here is my example at the bottom....I can get through the Tim's ok, but when I get to the Will's...notice he only has 1 line and when I go to select the (xldown) function or the line of code I sent ealier, it wants to grab the first row of Xlite's...That's where I'm getting lost. I don't Xlites at this point only Will...

                      Does this help any?

                      Thank you guys again


                      Tim 23
                      Tim 24
                      Tim 25

                      Will 26

                      Xlite 27
                      Xlite 28
                      Xlite 29

                      Comment


                      • #12
                        Re: Selection by row

                        ah i think i got it

                        do try:
                        Code:
                        Sub selectRange()
                        If Range("A2").Offset(1, 0).Value = "" Then
                        Range("A2").Select
                        Else
                        Set Range1 = Sheets("Sheet1").Range("A2:A" & Range("A65536").End(xlUp).Row)
                        Range1.Select
                        End If
                        Set Range1 = Nothing ' clear memory
                        End Sub
                        Cheers
                        ___________
                        Xlite
                        All you need to learn VBA is an internet connection and Ozgrid.com

                        Comment


                        • #13
                          Re: Selection by row

                          oops, still not there yet

                          let me try to understand again:


                          Tim 23
                          Tim 24
                          Tim 25

                          Will 26

                          Xlite 27
                          Xlite 28
                          Xlite 29

                          do u want to select tim only?
                          Cheers
                          ___________
                          Xlite
                          All you need to learn VBA is an internet connection and Ozgrid.com

                          Comment


                          • #14
                            Re: Selection by row

                            Yeah - with that example above, my most recent adaptation works, but only if the current selection is not one of the spaces between two data groups.

                            Another point - it seems as though you could re-organize your data and use a filter or something to get the desired result (i.e. instead of putting a space between different data groups, just filter the page so it only shows one group at a time, then do the other steps you need to do...)

                            Comment


                            • #15
                              Re: Selection by row

                              Yes to the Tim only ?...I tested it and it couldn't work any better at this point!



                              To the space question jm...I'm just going by how they give me their data or I would have programmed to take the space out and would have gone from there which I have done before but not with the space in between!

                              Very good point though jmhans It all works great!

                              You all are just to smart!

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X