Need VBA to delete cells based on criteria

[traugotts]

I am trying to write a VBA (I am a beginner so it's very difficult....)

I have an array of names in rTable A4 To B53 As Range. If a name changes to "VACANT" or "0" I want a subroutine to automatically delete the information from the corresponding row where the name once was.

The information to be deleted is only contained within certain columns and cells (C4:C53, G4:G53, K4:R53).

Any help is much appreciated!

[Arkadi]

Do you want to delete the whole row? or just blank the columns you mentioned (in the affected row of course)? When you say a name changes to vacant or 0... does that mean either A or B changes? or are the names in column A only?

[traugotts]

I only want to delete the columns/cells mentioned. The names are listed in both Column A & B; last name, first name, repsectively, so both columns would change if a name ever changes. (last name would become "VACANT", first name would become "0")

I don't know if this changes things, but I have the names linked to an outside workbook by HLOOKUP, hence the way they change. So the cells actually contain the formula but show the text.

[PCI]

See file attached with macro in sheet's code

[traugotts]

@PCI: when I try to open your doc it says parts were removed/repaired, and I try to view the code and there is nothing there....

Could be an issue with my security settings on this computer...I am at work :/

[PCI]

Try again, perhaps there was some conflict to access the file

Please Login or Register  to view this content.

[traugotts]

@PCI: I entered this code and I thought I ran it correctly, but nothing worked...

[PCI]

See my file attached. Is it working ?
Else send a sample of your file to install the macro

[traugotts]

I'm afraid it didn't work and I am unable to get the attachments to upload


Sorry... But thanks for the attempts to help!

[Arkadi]

You put the code in the worksheet, not a normal module right?

[traugotts]

I right click on Sheet 1 (which is where I want the code to run) and when I click view code, it is there. Maybe I'm not running it correctly?

[PCI]

Have a look in the file sent, how is it?

[traugotts]

copied and pasted exact.

[Arkadi]

maybe something failed earlier and enableevents is set to false?

[traugotts]

"Application.EnableEvents = False" on the first line and 'True' on the second

[Arkadi]

I just meant if it gets stuck on false somewhere, then the change event won't trigger until you set it to true again with some vba code. I've had that happen, I make a quick sub called test or something with just application.enableevents = true, run it and then all is well again.

[traugotts]

As a beginner (and I mean this is my very first time using it) to VBA I'm not sure I'm quite adept enough yet to know where it's getting stuck and re-write the code on my own....

[PCI]

Did you play with the file sent, is it working?
With your file
Before all treatments launch next macro to enable event
Macro to be put in a module

Please Login or Register  to view this content.

[traugotts]

Ok, if I place that code in a Module, when I go to run it, it pops up an error that says " argument not optional"

When the code was NOT in a module, just in "Sheet 1 (code)" I couldn't even find the code anywhere when I would go to run it. (Developer tab, Macro button....nothing showed up listed anywhere; I'd try to type in what I thought it was named and nothing.)

[PCI]

The last code must be put in a module
See file attached