none
Using Intersect with Dynamic Ranges to Branch RRS feed

  • Question

  • Excel 2016

    I have a spreadsheet in which data is entered in rows.  I would like a macro to fire when data is entered in column G, but only on the active row.  For example, if the last row with data is 2383, the macro should take action only if data is entered in to G2384.  After data is entered on row 2384, then the macro should take action only if data is entered in G2385, and so forth.  If data is entered or edited anywhere else the macro should not take any action.

    I was thinking about creating two ranges--one that covers column G, and one that is dynamically defined by the code and would cover the active row.  Those to ranges would intersect in column G, and so maybe I could use Intersect to make the code branch based on whether or not there is data in the cell at the intersection of the two ranges.  But I can't seem to get the syntax right to make that work.  Right now, what I have as a proof of concept is this:

        If Intersect(Range("A2381:G2381"), Range("G600:G2502")).Value <> "" Then
           
            MsgBox "It works!"
       
        End If

    That works, but the ranges are hard coded and I have not been able to make it work with range names.

    At this point, I am open to all suggestions, even if they are fundamentally different than the approach I am trying to make work.  What is the best way to achieve my goal?

    Thanks in advance for any help that you can offer!

    --Tom

    Wednesday, February 14, 2018 6:03 PM

Answers

  • Hi Tom

    If I understand your question correctly, you want your macro to fire when two conditions are met:  the cell to which data is entered is in column G, and the cell to which data is entered is one row below the existing bottom row.

    Try this:

    Put a change event into the active sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lngBottomRow As Long
    
        lngBottomRow = Cells(Rows.Count, 1).End(xlUp).Row
        If Target.Column = 7 And Target.Row = lngBottomRow + 1 Then
            Call ColGChange
        End If
    
    End Sub
    

    ColGChange should be replaced with your macro in the appropriate module.  To test, use this:

    Sub ColGChange()
    
        MsgBox "Run Macro"
        
    End Sub
    

    Hope this helps

    Andy C

    • Marked as answer by thomasm516 Thursday, February 15, 2018 4:52 PM
    Wednesday, February 14, 2018 10:27 PM

All replies

  • Hi Tom

    If I understand your question correctly, you want your macro to fire when two conditions are met:  the cell to which data is entered is in column G, and the cell to which data is entered is one row below the existing bottom row.

    Try this:

    Put a change event into the active sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lngBottomRow As Long
    
        lngBottomRow = Cells(Rows.Count, 1).End(xlUp).Row
        If Target.Column = 7 And Target.Row = lngBottomRow + 1 Then
            Call ColGChange
        End If
    
    End Sub
    

    ColGChange should be replaced with your macro in the appropriate module.  To test, use this:

    Sub ColGChange()
    
        MsgBox "Run Macro"
        
    End Sub
    

    Hope this helps

    Andy C

    • Marked as answer by thomasm516 Thursday, February 15, 2018 4:52 PM
    Wednesday, February 14, 2018 10:27 PM
  • Hi Andy,

    You understand my question correctly.  It looks like your suggestion is on the right track, but I have been unable to test it.  I pasted the code in to Sheet2, and when I hit F8 to step in to the code, the computer just beeps at me.  I did have another Private Sub Worksheet_Change(ByVal Target as Range) routine, but I commented out that entire block so that I could test your solution.  The workbook is macro enabled, and I as a test only, I enabled all macros by default, and even so the machine just beeps when I try to run the macro.

    Any ideas what might be wrong?

    Thanks,

    --Tom

    Wednesday, February 14, 2018 11:29 PM
  • Tom

    You cannot step through a change event using F8 from scratch.  If you wish to step through it, put a break at the line

    lngBottomRow = Cells(Rows.Count, 1).End(xlUp).Row

    then make a change in column G at the row below the bottom row of data in column A.  The code will stop at the break point, then you can continue to step through with F8.

    You will have to adapt the code to your needs.  For example, you may prefer to define lngBottomRow by the lowest line of data in column G (i.e. the 7th column) rather than column A (1st column).  You might wish to run your macro when data is entered into column G any number of lines below the last used row.  You may wish to deal with changes to column G when there is already data in there.

    Andy C

    Thursday, February 15, 2018 8:40 AM
  • Tom

    You cannot step through a change event using F8 from scratch.  If you wish to step through it, put a break at the line

    lngBottomRow = Cells(Rows.Count, 1).End(xlUp).Row

    then make a change in column G at the row below the bottom row of data in column A.  The code will stop at the break point, then you can continue to step through with F8

    DOH!  Now that you mention it, I remember doing that with the change event that I disabled in order to test your suggestion.

    So your code does work.  The only snag I ran in to is that I have formulas copied way down a couple of columns, and that initially caused the macro to consider the bottom of the formula columns as the last row.  I resolved that issue by modifying the macro as you suggested to key on a different column.

    Thanks for the help!

    --Tom

    Thursday, February 15, 2018 4:52 PM