Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: selecting mutiple rows

    and I was hopeing to only have 4 questions. anyway I need to select mutiple rows depending on color I was working and I got this far but once selected I don't know how to keep it selected when I move onto the next row

    Code:
    if interior.colorindex = 35 then
         do while interior.colorindex = 35
              entirerow.select
              next row
         loop
    else
         next row

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    are they in a block or are they seperate at this time

    if they are in a block hold referances to the upper cell and the lower cell
    do something like this

    Code:
    Sub test()
        Dim r1 As Range
        Dim r2 As Range
        Dim I As Long, Lastrow As Long
        
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
        If ActiveCell.Interior.ColorIndex = 35 Then
            Set r1 = ActiveCell
            For I = r1.Row To Lastrow
                If Not ActiveCell.Offset(I, 0).Interior.ColorIndex = 35 Then
                    Set r2 = ActiveCell.Offset(I - 1, 0)
                    Exit For
                End If
            Next I
        End If
        
        
        Range(r1, r2).EntireRow.Select
        
    End Sub
    replace activecell with however you picked your first range

    If they are seperate you would have to hold each cell refrence in an array then put the ranges in a union select

    HTH

    David

  3. #3
    Join Date
    Jul 2004
    Posts
    34

    little problem

    That code had a little problem. well two. First It selected everything from the active cell through the rest of the spreadsheet. Second if the active cell was half way down the color it only highlighted the bottom half it didn't get the top half of the color. so If I sorted I would only sort the bottom Half of the color. (and everything else below the active cell curently.

    OK so here is what I have I have a list of part numbers all one color. They are all together and blocked. I just need to sort that color by the partnumbers. I have about 9 different color blocks of partnumbers

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    ok i have looked quickly at what ive done and spoted a few errors myself,
    but we are getting there, Im at home at the mo and don't have excel here ill look more in depth tomorrow for you, here are a few quick suggestions


    first put replace

    For I = r1.Row To Lastrow

    to For I = 1 To Lastrow - r1.row

    to get r1 you will have to add an extra loop to look up in the first place so add in

    Code:
    For I = r1.Row To 1 Step -1
                If Not ActiveCell.Offset(-I, 0).Interior.ColorIndex = 35 Then
                    Set r2 = ActiveCell.Offset(-I + 1, 0)
                    Exit For
                End If
            Next I
        End If
    Next I
    for the sorting purposes we don't even need to select do something like

    Range(r1,r2).entirerow.sort range("B" & r1.row)

    let me know if this works any better for you

    Dave

  5. #5
    Join Date
    Jul 2004
    Posts
    34

    Thumbs down glaaaaaaarrrrrrggggghhhhhh

    ok so yeah I thought the sort part at the end would be a sinch as soon as I had the color all together....... Boy was I wrong this just might be the most complex part of my code. Deffinatly has more variables than any other part....

    here is what we have so far

    Code:
    Sub sortcolorgroup()
        Cells.find(updatepartnum, ActiveCell, xlFormulas, xlPart, xlByRows, xlNext, False).Activate
        
        Dim r1 As Range
        Dim r2 As Range
        Dim I As Long, Lastrow As Long
        
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
        If ActiveCell.Interior.ColorIndex = colornum Then
            Set r1 = ActiveCell
            
    '        For I = r1.Row To 1 Step -1
    '           If Not ActiveCell.Offset(-I, 0).Interior.ColorIndex = colornum Then
    '                Set r2 = ActiveCell.Offset(-I + 1, 0)
    '                Exit For
    '            End If
    '        Next I
    
            For I = 1 To Lastrow - r1.Row
                If Not ActiveCell.Offset(I, 0).Interior.ColorIndex = colornum Then
                    Set r2 = ActiveCell.Offset(I - 1, 0)
                    Exit For
                End If
            Next I
        End If
            
        Range(r1, r2).EntireRow.Select
        Range(r1, r2).EntireRow.Sort Range("B" & r1.Row)
          
    End Sub
    the problem is the look up part which I have commented out currently. is it in the wrong place?

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ive done some work on this for you and have tested this slightly modified code

    Code:
    Sub sortcolorgroup()
        Dim r1 As Range
        Dim r2 As Range
        Dim r3 As Range
        Dim colornum As Integer
        Dim I As Long, Lastrow As Long
        
        'get the lastrow filled with data
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
        'find the first cell with info in it in Column B
        Set r1 = Range("B2:B" & Lastrow).Find(updatepartnum, LookAt:=xlPart)
        'check to see if exsits
        If Not r1 Is Nothing Then
            colornum = r1.Interior.ColorIndex
            'find the upper part of the range
            For I = 1 To r1.Row - 1
               If Not r1.Offset(-I, 0).Interior.ColorIndex = colornum Then
                    Set r2 = r1.Offset(-I + 1, 0)
                    Exit For
                End If
            Next I
            
            'find the lower part of the range
            For I = 1 To Lastrow - r1.Row + 1
                If Not r1.Offset(I, 0).Interior.ColorIndex = colornum Then
                    Set r3 = r1.Offset(I - 1, 0)
                    Exit For
                End If
            Next I
            
            'sort the range
            Range(r2, r3).EntireRow.Sort Range("B" & r1.Row)
        Else
            MsgBox "Part Number Not Found"
        End If
        
        'Clear object Variables
        Set r1 = Nothing
        Set r2 = Nothing
        Set r3 = Nothing
    End Sub
    I was to quick about posting last time didn't test and see where that gets us,
    This code should now work perfectly(well i hope at least )
    and not a select or an activecell in sight, if you have any questions about the code just post back and ill try to explain further

    Dave

  7. #7
    Join Date
    Jul 2004
    Posts
    34

    edge of perfection

    So yeah you are really good at this. Only your comment about no select or active cell worries me. For the entire rest of my code, except my sort by color and sort color group functions, I think I called out 5 variables and ran the rest off activecell. I suspose that either makes me really smart or really stupid. ..... Anyway back to the problem at hand. Your code for sortcolorgroup worked really really great untill the last section which happens to be colorless or colornum = xlnone. Don't know why it just Doesn't seem to want to handle my colorless section. I would just simply change it and give the section a color, sept it's not my spreadsheet and I am just writing the Macro for the boss. Let me know what you come up with. Thanks a lot!

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by VBALordCorp
    I suspose that either makes me really smart or really stupid.
    neither it, just means that your just starting out, it seems the most logical way to do things is to use selects and activecells but it slows your code down quite horribly,

    Quote Originally Posted by VBALordCorp
    So yeah you are really good at this.
    practice is all it takes, my best advice is to try something out maybe a post to a forum try to answer that question and then check back what other people have posted and compare

    Quote Originally Posted by VBALordCorp
    Your code for sortcolorgroup worked really really great untill the last section which happens to be colorless or colornum = xlnone. Don't know why it just Doesn't seem to want to handle my colorless section. I would just simply change it and give the section a color, sept it's not my spreadsheet and I am just writing the Macro for the boss. Let me know what you come up with. Thanks a lot!
    have you tried color -4142, that is the numeric equivalent for no colour
    or a quick soloution might be to change the colour sort then change back once your done or if you have a distinct pattern i.e. colourless last and green always above, search for the last green cell and go down one cell, or you could add a column back in with the colorindex and search for the first and last instance of -4142 then sort then delete the column back out again

    if you want me to produce some code decide which option to go for then just ask and i will be happy to produce some for you and ill promise test it first

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •