Results 1 to 2 of 2

Stop action on rows removal

Threaded View

  1. #1
    Registered User
    Join Date
    08-01-2017
    Location
    prnu
    MS-Off Ver
    win 10
    Posts
    15

    Stop action on rows removal

    Hello I need your help ro solve how to stop macro (do nothing) when user is selecting several rows in range and delete it.

    I have simple code what execute on target cell value change. But i want this code not to rune when user deletes row what is containing this cell.
    Thanks

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
        Dim lookupcell As Range
        Dim cqty As Integer
        Dim countifrange As Range
        
        
        Set KeyCells = Range("c40:C100")
        Set countifrange = Worksheets("Kaablid").Range("A2:A500")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
    
    
            Set lookupcell = Worksheets("Kaablid").Range("A2:A500").Find(Target)
    
            cqty = Application.WorksheetFunction.CountIf(countifrange, Target)
            If cqty = 0 Then Exit Sub
    
            
            Application.EnableEvents = False
            ActiveCell.EntireRow.Offset(1).Resize(cqty).Insert Shift:=xlDown
            Application.EnableEvents = True
    
    
        End If
    
    End Sub
    Last edited by Nooclear; 07-19-2018 at 07:49 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Stop seemingly default action of copied activex component
    By TravelByFireworks in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2018, 02:04 PM
  2. automatic removal of empty rows
    By mr.remizov in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2017, 07:04 AM
  3. My Action Tracker Keeps placing my competed action rows on sheet2 in the same place.
    By billybong33 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2014, 04:01 PM
  4. [SOLVED] How to stop a Loop and start new action
    By rlkerr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-19-2012, 01:13 PM
  5. Replies: 1
    Last Post: 01-20-2012, 07:17 AM
  6. Excel 2007 : marking rows for removal using a formula
    By carrach in forum Excel General
    Replies: 3
    Last Post: 09-08-2011, 07:29 AM
  7. [SOLVED] Removal red rows in excel
    By Jimmy Ionic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2006, 06:35 AM

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