Posts by oracle259

Re: How to detect row insert or delete

Thanks I will give this a try

How would I go about using vba to detect a row insert or delete

I wrote some code recently and then renamed the sheet and the code stopped working. My question is
How do I ensure that renaming the Sheets does not affect my coding. For example if I renamed sheet 1, Data.
Is there a best practice in how to write the code to avoid this problem

Re: Finding the value of a cell in one column using the cell reference of another col

Thanks you nailed it

Re: Finding the value of a cell in one column using the cell reference of another col

Thanks code works perfectly. Is there a way to use intersect to do the same

Re: Enable Multi-Sheet Deletion

Thanks so much for sticking with me. I learnt so much. I played around with the code and got it to work using hyperlinks. See sample worksheet attached. Code is a follows

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ws As Worksheet
    Dim foundID As Range
    Dim acVal
    Dim searchFind
    
 
    If ActiveCell.Row() > 1 And Target.TextToDisplay = "Delete" Then
    acVal = Target.Parent.Row
    searchFind = Cells(acVal, 1).Value
    '  MsgBox searchFind
       
    For Each ws In Sheets
     
        Set foundID = Sheets(ws.Name).Range("A:A").Find(searchFind, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundID Is Nothing Then
            foundID.EntireRow.Delete
           ' MsgBox ("Found: " & searchFind)
        End If
    Next ws
       
    End If
    
    
    Application.ScreenUpdating = True
End Sub

How do I find the value of a cell in one column using the cell reference of another column

For example if $A$2 value is brick how can I MSGBOX "BRICK" using the Cell Reference $G$2

Re: Enable Multi-Sheet Deletion

So sorry I didnt see this post. I tested the new code and it works like a charm. I will investigate why I doesn't work for the others and play around with it some

Re: Enable Multi-Sheet Deletion

Thanks. I tested them both but they only delete the ID from Sheet 1 only. The formulas are not as important so I will find a work around. But the multi-page deletion is essential. Not sure if it helps but I am using excel ver 14.0.7116.5000 (32 bit). Maybe that's why the code isn't working for me

Re: Enable Multi-Sheet Deletion

I tested it but it only deleted the ID on sheet 1 only

Re: Enable Multi-Sheet Deletion

The entire row.

Re: Enable Multi-Sheet Deletion

The entire row. The aim is to use the Id to delete the entire row content

Re: Enable Multi-Sheet Deletion

Any ID that is deleted from sheet 1 is what needs to be deleted from sheets 2 and 3

How can I delete information on multiple sheets on Sheet Activation. Using the attached workbook as an example how do I delete an ID number on sheet 1 have have the row with the records associated with that ID number deleted from Sheets 2 and 3 when they are activated.

Re: Delete multiple cells from a column based on their value

Sorry got caught up in Pike's Golden Rules. Good stuff & I have a long way to go yet. The codes work I will have to spend some time learning their application in various scenarios. Thanks everyone and I will post more sample workbooks with my questions. Thanks guys

Re: Delete multiple cells from a column based on their value

It does execute the deletion but it throws an error when there are no more values greater than 1. It then deletes the header row and creates a filter for column B.

Re: Delete multiple cells from a column based on their value

Thanks. Got caught up with an assignment at work. I tested the code it works but not quite sure what it does. Also clueless how to integrate it with the following code.

b = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
  
For i = 1 To b
    ' // Copy over missing primary key.
    Sheet1.Range("A" & a & ":" & "A" & a).Copy _
   Destination:=Sheet2.Range("A" & a & ":" & "A" & a)
    ' // Check if primary key is unique.
    Range("A" & a, Range("A" & Rows.Count).End(xlUp)).Offset(, 1).Formula = "=COUNTIF(Sheet2!$A2:$A" & a & ",$A" & a & ")"
    
[COLOR=#ff0000][B]'// Insert Code to delete all cell in Column B with values greater than 1.[/B][/COLOR]
    
 Next i

What is the quickest and easiest way to delete multiple cells from a column based on their value. Say u have 10 cells in Sheet 1 Column A of 5 cells have values greater than 4 how do u delete the ones with value less than or equal to 4

Re: Copy column data from one sheet to another and delete duplicate entries

It does execute the copy, checks and deletes duplicates. But it also deletes the data in the first row A2 (A1 contains Headers)

Re: Ensure that Primary ID is unique

Can u suggest a way to test this code. Everything I tried creates an endless loop