Results 1 to 5 of 5

Subroutine to delete a row on double click gone wild!

Threaded View

  1. #1
    Registered User
    Join Date
    07-28-2014
    Location
    Kota Kinabalu, Borneo
    MS-Off Ver
    2011 Mac
    Posts
    16

    Angry Subroutine to delete a row on double click gone wild!

    Hi folks:
    A while back I came to the list with some code to delete a row in a second worksheet based on a deletion of a row in an active cell. I had some great help and guidance from mikerickson and the resulting code looks like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Set sourcebook = ThisWorkbook
        Set sourcesheet = sourcebook.Worksheets("AC CODES")
        Set targetbook = ThisWorkbook
        Set targetsheet = targetbook.Worksheets("EXPENDITURES SUMMARY")
        Dim dynamiccounter As Integer, myRow As Long
        Dim staticcounter As Integer
        myRow = Target.Row
        dynamiccounter = sourcesheet.Range("BA1").Value
        staticcounter = sourcesheet.Range("BB1").Value
            If dyamiccounter < staticcounter Then
                Application.EnableEvents = False
                targetsheet.Rows(myRow).EntireRow.Delete
                sourcesheet.Range("BB1").Value = dynamiccounter
                Application.EnableEvents = True
            End If
    End Sub
    Just as an FYI the counters represent the following: dynamiccounter is the number of rows used by a range I have named. staticcounter is set to equal the numeric value of dynamiccounter to start with, and then changes depending on whether the number of rows changes as per the code line: sourcesheet.Range("BB1").Value = dynamiccounter

    The subroutine deletes a row where I want it to with ease. It works great.

    The problem is a row gats deleted ALSO whenever I type text, or change a font, or do anything....! I am frustrated beyond compare. As far as I am aware I am asking the system to ONLY delete a row if the counters differ in number, and these counters do not change value when a font is changed or when text is written into a cell. They sit quietly to the side doing nothing until one row gets deleted. Only then do the values change and this triggers the deletion in sheet two. So why would a row in sheet two be deleted if I type a word or change a font?

    I REALLY could use your help!
    Nick
    Last edited by npilcher; 08-13-2014 at 08:06 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ADD/DELETE Cell content on double click
    By belen373 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-30-2014, 07:11 PM
  2. Changing cell properties with double click, then revert with another double click
    By mweber2525 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-01-2014, 01:40 PM
  3. Match and delete row with double click
    By johnny_p in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 10-14-2013, 05:12 PM
  4. Userform: delete all content through double click but not the highlighted line
    By wali in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-10-2010, 05:09 AM
  5. Textboxes - restrict double-click and selection.delete
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2006, 04:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1