0

I want to activate a macro on a change in a range.

The following code works fine except I want a variable last row (Where B100 currently is).

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
        MsgBox "Updating sheets"
        Call Thickborders2

    End If
End Sub

The B100 in the range is dependent on the last row with text in it.

Community
  • 1
  • 1
Jeijnes
  • 3
  • 3
  • 2
    `Range("B1:B" & IntLastCell)` should work I think, problem is the code still needs to find what is the last cell although this could be done with a while loop going down until the cell is empty and assigning that row number to `IntLastCell` – Taazar Dec 18 '18 at 09:44
  • 1
    Your problem is finding the last row with text in it. Various approaches are outlined [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). – L42 Dec 18 '18 at 09:48
  • I was going to suggest changing your range to `Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))` but the event fires after you've added a value so if you add data to row 1048575 (the code doesn't work on row 1048576) it will adjust to take that as the last row containing data. I think that will be a problem with any suggestion. – Darren Bartrup-Cook Dec 18 '18 at 10:46

3 Answers3

0

You can borrow a worksheet trick to find the last row with text in it.

=MATCH("zzz", B:B)

The above returns the last row in column B with a text value.

Private Sub Worksheet_Change(ByVal Target As Range)

    dim m as variant
    m = application.match("zzz", columns("B"))
    if iserror(m) then m = 1

    If Not Intersect(Target, Range("B1").resize(m, 1)) Is Nothing Then

        MsgBox "Updating sheets"
        Call Thickborders2

    End If
End Sub

I strongly recommend adding error control (on error goto <label>) and disabling event triggers (application.enableevents = false). Remember to re-enable events before exiting the sub.

  • A possibility - if you add `Application.EnableEvents = False: Application.Undo` before you calculate `m` and `Application.Repeat: Application.EnableEvents = True` after it you will get the last row based on the values before you made the change. Also shouldn't `""zzz""` be just `"zzz"`? – Darren Bartrup-Cook Dec 18 '18 at 11:42
  • Thanks for catching the doubled-up quotes where they weren't needed. As mentioned above, Undoing a deletion or clearing of rows at the bottom of the data block might not be desired. –  Dec 18 '18 at 11:46
  • You are already solving problems I didn't even know I had yet. It is, however, needed to count the rows before the change was made. I don't want the sheets to update halfway through adding a new row. `Application.repeat` gave me an error. I did the following instead: `Application.EnableEvents = False: Application.Undo` then `m` then `Application.Undo: Application.EnableEvents = True` – Jeijnes Dec 18 '18 at 12:33
0

As I said in my comment on the OP - Worksheet_Change on its own won't work as it will calculate the last cell based on the data just entered.

This code calculates the last cell when you move cells (I tried on the Calculate event but that happens after you've added the data so same problem as the Change event). 

Option Explicit

Private rLastCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set rLastCell = Cells(Rows.Count, 2).End(xlUp)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(Cells(1, 2), rLastCell)) Is Nothing Then
        MsgBox "Updating sheets"
        Call Thickborders2
    End If
End Sub

The first two lines must be at the very top of the module.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • If that was on Sheet1 and I ran a sub procedure that moved or copied some rows from Sheet2 to Sheet1 without relying on Select or Activate on Sheet1 then rLastCell is not going to hold the correct 'last row'. –  Dec 18 '18 at 11:25
  • @user10798192. That's true. If you moved data to `Sheet1` you'd have to use some other method to update the last row. I'm trying to find a way around the problems with just using `Worksheet_Change` - e.g. with your code if I type a letter into `B1048570` it accepts that as the last row even though the rest of the column is blank so you might as well ignore the last row bit and just accept the whole column. – Darren Bartrup-Cook Dec 18 '18 at 11:33
  • 1
    Yes, using full column would make sense. The only exception might be deleting or clearing rows. The OP's helper sub is called *Thickborders2* so I'm guessing it shouldn't be called if you were just deleting some rows at the bottom. TBH, there really isn't much to go on. –  Dec 18 '18 at 11:43
-1

Building on comments from Taazar and L42 try:

Private Sub Worksheet_Change(ByVal Target As Range)
    LastCell = Activesheet.Usedrange.Rows.Count
    If Not Intersect(Target, Range("B1:B" & LastCell)) Is Nothing Then
        MsgBox "Updating sheets"
        Call Thickborders2
    End If
End Sub

Where Activesheet should be replaced by the sheetname you're checking.

Jason Stallard
  • 349
  • 2
  • 15
  • 2
    `Usedrange` is an unreliable way to find last row... Check the link which @L42 has given above... Also if you are using `Worksheet_Change` then depending on what `Call Thickborders2` has you may have to switch off events and use error handling as show [HERE](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Dec 18 '18 at 10:09
  • **Never** use Activesheet to refer to the parent worksheet of a Worksheet_Change event procedure. There is no guarantee that worksheet is the Activesheet . No parent worksheet reference is necessary since any Range or Cells object automatically inherits the parent worksheet. If you **have** to reference the parent worksheet then use `Me` as the reference. –  Dec 18 '18 at 10:30