Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    I have a worksheet change event in development that is already large enough it visibly slows my system down. The change event iterates through about 500 rows of data based on the user entries. I am using the change event to guide the user in what entries are valid vs. invalid with colorcoding. There are several dozen comparisons and evaluations being done on each row. The total effect is that it takes about 4 seconds for the VBA to complete processing each time new data is entered.

    Each row is evaluated independent of every other row so every time this process runs only 1 row is actually being changed. The other 499 are being processed unnecessarily.

    Is there a way to setup a change event so that it targets only a particular row or range of cells instead of the entire worksheet?

    Regards,

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,720
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    The change event is passed an argument, Target, which is the range that has been changed.

    If you only want to process what's been changed use that argument in your code.
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    432
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    he change event iterates through about 500 rows of data based on the user entries.
    Are you iterating down the worksheet ?, because if you are you could speed it up enormously by using variant arrays to do the checking. On your worksheet change event load the enitre worksheet into memory and then iterate through the variant array.
    for example
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    inarr = Range(Cells(1, 1), Cells(500, 200))
    for i = 1 500
     if inarr ( i ,1) = "garbage" then
     ' do something
    end if
    next i
    
    End Sub
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,625
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    Show us your change event script.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    I am iterating through a 33x500 cell matrix.

    Here is the code:

    Code:
     Private Sub worksheet_change(ByVal Target As Range)
     
      
     For i = 0 To 500
      'A bunchof conditional statements
    Next i
    
    
    
    
    End Sub

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,803
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    I think it's possible to do what you want, but we need more details. Anyway, see if this example helps
    Say you have data in columns A1:E10 and every time the user changes a value in A1:A10 you want to check if there is an empty cell in the correspondent row (Bx:Ex)

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rCell As Range
        
        If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
            For Each rCell In Range("B" & Target.Row & ":E" & Target.Row)
                If rCell = "" Then
                    rCell.Interior.Color = vbYellow
                Else
                    rCell.Interior.Color = xlNone
                End If
            Next rCell
        End If            
    End Sub
    M.

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,720
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    Are you referring to Target in the code anywhere?

    If you aren't then it kind of hard to see why you would use a change event.
    If posting code please use code tags.

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    Here is a larger snapshot of my code. I am not including the whole thing because it just gets cumbersome to look at.

    I have been using the Range.offset method of specifying and cells. I am new to VBA writing (about two weeks in so far) and do not know if that is the best way to go about this.

    My data inputs run from Column O to column AG. Everything before column O is driven by the spreadsheet. My user manipulable section runs from O15:AG515. My complete matrix runs from B15 to AG515.

    I would like to set this up so that if the user makes a change on row 25 then it only runs the comparisons and changes colors for row 25.

    Code:
    Private Sub worksheet_change(ByVal Target As Range)
     
        blue = RGB(217, 225, 242) 'sets the color for cells that allow input.
        ggray = RGB(150, 150, 150) 'sets color gray, applied to cells that conditionally allow input when they are not accepting.
        yellow = RGB(255, 255, 0) 'sets cell color to yellow, applied when a room name does not resolve.
        red = RGB(255, 150, 150)
        blank = RGB(255, 255, 255)
        override = RGB(255, 238, 183) 'a peach color for cells where the user inputs an override.
        green = RGB(150, 255, 150)
      
     For i = 0 To 500
        
        
        
        'This set controls the color status of the Min Required Exhaust cells, Plan return and Plan Exhaust cells, HEPA cells (M, N, R & S)
        If Range("M15").Offset(i) = "" Then                                         'A quick check to see if the Min Req'd Exhaust column is empty
            m = 0                                                                   'Provides a value for the comparison in later statements
            Else: m = Range("M15").Offset(i)
            End If
            
        If Range("G15").Offset(i) = "No" Then
            If Range("N15").Offset(i) = "YES" Then                                          'If HEPA filtration is allowed
                Range("N15").Offset(i).Interior.Color = blue
                Range("S15").Offset(i).Interior.Color = override
                
                If Range("R15").Offset(i) = "" Then                                     'Blank exhaust cell turns red.
                        If m <> 0 Then
                            Range("M15, R15").Offset(i).Interior.Color = red
                            Else
                                Range("R15").Offset(i).Interior.Color = red
                                Range("M15").Offset(i).Interior.Color = blank
                            End If
                    ElseIf (Range("R15").Offset(i) + Range("S15").Offset(i)) >= m Then              'Check to see if exhaust + return exceed required exhaust
                        Range("R15").Offset(i).Interior.Color = blue
                        Range("M15").Offset(i).Interior.Color = blank
                        Else
                            Range("M15, R15").Offset(i).Interior.Color = yellow                 'Otherwise they are both yellow
                    End If
    
    Next i

  9. #9
    New Member
    Join Date
    Jun 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    Norie,

    I am not using Target yet though it seems I should be.

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,625
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Is there a change event type that works on specific rows or ranges instead of on an entire worksheet?

    Here is a example of a sheet change event script.
    If you enter the word George in column A
    This script will run:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "George" Then
        With Target
            .Offset(, 1).Value = "Me"
            .Offset(, 3).Value = "You"
            .Offset(, 5).Value = "Us"
            .Offset(, 7).Value = "All Of Us"
        End With
    End If
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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