Deleting specific cells when a condition is no longer met

[Serenutty]

Hi all

I'm completely new in the forums world. I did a little bit of vba many years ago and now I have been task with something impossible for me but I hope it will be easy for you experts.

Firs I need to say that I have posted this thread in other forums and I will provide links but I have not yet found a solution. I didn't mean to cross-post and I have been found out but I did it unintentionally and have already apologise in the other forums so I hope you will still help me.

My initial post was solved but a question arising from it has not been solved yet so I'm trying this forum to see if any of you are interested and find a solution. I certainly would appreciate it.

Original question was to copy specific cells from sheet 1 to sheet 2 when a condition on sheet 1 was met by selectin one choice of a dropdown menu. That code produced by a forum expert worked perfectly. But, when the condition changed on sheet 1 and didn't meet the pre-requisite to be copied in sheet 2 but had already been copied because the pre-requisite had been previously chosen and met, that row was not deleted from sheet 2, thus making the data inaccurate. I hope I explain myself well, otherwise ask me please.
So another forum expert came up with a brilliant solution, which is to automatically create a unique identifier with date and time and place it in column XX. Unfortunately when I copy this code it doesn't work for me and doesn't even copy the specific cells on sheet 2. Legend is on sheet 3.
I post the code below and attach the spreadsheet to see if some of you can spot why it doesn't work for me. I would greatly appreciate help to solve this.

[Private Sub Worksheet_Change(ByVal Target As Range)

'Modified 1-5-2018 1:30 PM EST

On Error GoTo M

If Target.Count > 1 Then Exit Sub

Dim c As Range

Dim Lastrow As Long

Lastrow = Sheets("Costing").Cells(Rows.Count, "XX").End(xlUp).Row + 1

If Target.Column = 12 And Target.Value = "Exit from business or transfer to another role outside current BU or Function - no backfill" Then

Cells(Target.Row, "XX").Value = Now()

Range(Cells(Target.Row, 1), Cells(Target.Row, "H")).Copy Sheets("Costing").Cells(Lastrow, 1)

Sheets("Costing").Cells(Lastrow, "XX").Value = Now()

Else

If Target.Column = 12 And Target.Value <> "Exit from business or transfer to another role outside current BU or Function - no backfill" Then



For Each c In Sheets("Costing").Range("XX1:XX" & Lastrow)

If c.Value = Cells(Target.Row, "XX").Value Then Sheets("Costing").Rows(c.Row).Delete

Next

Cells(Target.Row, "XX").Value = ""

End If

End If

Exit Sub

M:

MsgBox "Sorry we had some type problem. Try again"

End Sub]

The link with relevant thread is https://www.mrexcel.com/forum/excel...t4981259.html?t=1037655&p=4981259#post4981259

Please note that I made a test spreadsheet, so the name for sheets and text to select copying cells are different in this thread. But the code doesn't work in my test spreadsheet either, so I thought I would post the real spreadsheet to avoid any more chances to errors.

Both spreadsheets attached, test and real

thank you and I hope you can help

 

[Serenutty]

Oops! Files attached here

 

[DianaTurner]

Thank you for your information about deleting particular cells, i need to dealing with spread sheet as part of my job. This post help me to understand stand more details. advantage of essay writing service

 

[vletm]

Serenutty
I did some modifications to code with 'ELT-3 ....'-file. Do it work now?
or
Would You try to explain one more time step-by-step
with samples in file (ex one step per row)
what would You like to get?

 

[Serenutty]

Hi vletm,

Thanks for taking the time and your code. It seems to be copying OK but there are errors e.g. it doesn't delete the right record when changing from "Exit ..." to "No exiting" and the formula in columns to the right are lost and error #REF! appears.

I'm attaching file so you can see.
Also, I want to make you aware that the forum expert in my original post in the other forum (here is the link https://www.mrexcel.com/forum/excel...t4981407.html?t=1037655&p=4981407#post4981407) replied this morning. I also replied but I am copying my comprehensive reply with code and file here for ease.

Thank you so much and I hope I am not making you waste your time. I am new and made the mistake of posting in more than one forum because in my initial post two people that reply, stopped replying with no explanation; although that could be becasue they thought the problem was solved. So I opened a second thread with the current problem and another experts replied to my second post and he has been consistent in replies so I want to make you aware of the situation.

Here is my reply with code at bottom from this other expert

I’m happy to say that your code now works perfectly but strangely I’ve had to open and close the spreadsheet 3 times for it to work correctly, without changing the code.

I don’t know how to express how amazed I am J

I explain the process below because I don’t know if this is normal.

To answer your questions:

1. If on sheet named “Client” and you have the code in this sheet you enter the value "Yes" in column “L” then this row of data should be copied to sheet named “Decision” – Yes

2. Now if you go back and change “Yes” in column “L” on sheet named “Client” to something else like “No” or “Duck” then this same row on sheet named “Decision” should be deleted. – Yes, deleted from sheet 2 “Decision” only

3. The row on sheet named “Client” will never be deleted. – Correct

4. And your using the last script I sent you dated 'Modified 1-5-2018 1:30 PM EST – Yes I was using this one when I said it wsn’t copying but now I’m using the one you sent this morning at Today, 04:13 AM and some of it works. I explain below after answering your questions as requested.

5. I want us working with the word “Yes” for now till we figure this out. There should be no mistake using this simple word. Later when it starts working we can change the word back to what you want. – I understand
6. And your saying nothing is now working. The row is never copied to the sheet named “Decision” is that correct? Yes, that was correct with code 'Modified 1-5-2018 1:30 PM EST but with code Modified Today, 04:13 AM it works but not completely correct. (Wow! I’m really amazed) - I have opened a new spreadsheet and copied the code you sent me in this last email Modified Today, 04:13 AM and changed the legend to "Yes" in “Legend” sheet

I will explain here how it works and what goes wrong:

1. It correctly copies the correct cells from sheet 1 “Client” when “Yes” it’s chosen and pastes them in the right place in sheet “Decision” –Progress J

2. I have created 9 rows in sheet “Client” with “No exiting” and nothing gets copied. This is the correct outcome

3. Then I randomly changed some rows in sheet “Client” with “Yes” and all are copied into sheet “Decision” correctly but when I changed them again to “No exiting” some errors occurred e.g. deleting the title row, or not deleting row 2 from “Decisions”, then when I changed to “No” and back to ”Yes” it copied a new row 2, showing twice in sheet Decisions.

4. So I decided to go row by row in a new fresh version of the spreadsheet to see at what point it went wrong to explain to you

5. I started from row 2 changing all to “Yes” – copied correctly. Row 1 has titles

6. I have changed again row 9 to “No exiting” and deletes it perfectly. I have then changed to “No exiting” rows from 8 to 2 in that order and they have been deleted correctly.

7. Then I tested it again but changing to “No exiting” from 2-9 in that order and they have been deleted correctly

8. Then I’ve changed again random rows to “Yes” and then changed back to “No exiting” – and now it seems to work perfectly.

9. I’ve tried this in 3 different spreadsheets. On the 3rd one it worked correctly. Wow and double Wow!

Then I transferred your code to my real spreadsheet – Apologies for this but I thought it would be easier to test on another spreadsheet. Now I realise it was a mistake. I’m learning so please bear with me. There’s a copy of the code I have used at the bottom of this message.

I changed to real names “Client” = “Pool” and “Decision” = “Costing” and “Yes” = “Exit from business or transfer to another role outside current BU or Function - no backfill”. It works but the same errors occurred:

First time it wasn’t copying, closed and opened spreadsheet, it copied but at some point deleted the first row with titles in sheet 2 “Decision” = “Costing”.

Closed the spreadsheet and opened again, added title row and it started copying and deleting from sheet “Costing” correctly except for row 2 which was not deleted in sheet “Costing”. I changed the option back and forth several times for row 1 and it copied it twice in “Costing” but when changing from “Yes” to “No” again it deletes one of the rows from record 2 in sheet1 “Pool”.

Opened and closed spreadsheet, it works perfectly except that there are some formulas to the left columns from L column and these formulas are lost and shows as #REF! every time I switch from “Yes” to “No” and viceversa. It even messes up the formula incrementally in rows everytime I switch.

I hope you can shed some light as to why this happens. I hope a formula is not a script because you did ask me if there were any more scripts and said no but I didn’t know any better

Level pre integration

Talent pooled

Appointed to ELT-3 role

New BU or Group Function

Exiting

Synergy Saving

Exit/Transfer Date

Total cost to achieve

Annual Savings (£,000's)

ELT-1




Not exiting

0


Cost to achieve £'000's

Annual Savings £'000's

ELT-2




Not exiting

0


0

0

ELT-3




Not exiting

0


#REF!

#REF!

ELT-4




Not exiting

0


#REF!

#REF!

ELT-5




Not exiting

0


#REF!

#REF!






0


#REF!

#REF!






0


#REF!

#REF!






0


#REF!

#REF!






0


#REF!

#REF!






0


#REF!

#REF!






0


0

0



This is the code I have used for my real spreadsheet:

[Private Sub Worksheet_Change(ByVal Target As Range)

'Modified 1-5-2018 11:10 PM EST

On Error GoTo M

If Target.Count > 1 Then Exit Sub

Dim c As Range

Dim Lastrow As Long

Lastrow = Sheets("Costing").Cells(Rows.Count, "XX").End(xlUp).Row + 1

If Target.Column = 12 And Target.Value = "Exit from business or transfer to another role outside current BU or Function - no backfill" Then

Cells(Target.Row, "XX").Value = Now()

Range(Cells(Target.Row, 1), Cells(Target.Row, "H")).Copy Sheets("Costing").Cells(Lastrow, 1)

Sheets("Costing").Cells(Lastrow, "XX").Value = Now()

Else

If Target.Column = 12 And Target.Value <> "Exit from business or transfer to another role outside current BU or Function - no backfill" Then



For Each c In Sheets("Costing").Range("XX1:XX" & Lastrow)

If c.Value = Cells(Target.Row, "XX").Value Then Sheets("Costing").Rows(c.Row).Delete

Next

Cells(Target.Row, "XX").Value = ""

End If

End If

Exit Sub

M:

MsgBox "Sorry we had some type problem. Try again"

End Sub]

Second answer:


More results from testing:

I’ve closed spreadsheet and re-opened and made a few changes between No and Yes, these were correct but on the third change when changing the first record (row2) my headings on “Costing” were deleted again.

Then I changed row 2 on “Pool” and was copied on row2 in “Costing” but there were no headings. I further changed row5 from No to Yes and was copied in rwos 1 and 2 in “Costing”

Every time I change from Yes to No it messes up a formula and it doesn’t follow a sequencial order.

Changing from No to Yes does not mess up the formula.


I have discovered the sequence that it follows when messing up the formulas:

Always when changing for yes to No and it skips as many rows as there are in “Costing”

Costing tab

Legacy Company

Legacy Business Area

Legacy Business Line or Function

Role

Incumbent

Country

Location

Exit Date

AFW

OGC - DS CP EMEA

E&I

4

4

4

4

ELT-4

WG

OGC - DS CP EMEA

OGC

2

2

2

2

ELT-2










Pool tab

Level pre integration

Talent pooled

Appointed to ELT-3 role

New BU or Group Function

Exiting

Synergy Saving

Exit/Transfer Date

Total cost to achieve

Annual Savings (£,000's)

ELT-1




Not exiting

0


Cost to achieve £'000's

Annual Savings £'000's

ELT-2




Exit from business or transfer to another role outside current BU or Function - no backfill

1


#REF!

#REF!

ELT-3




Not exiting

0


0

0

ELT-4




Exit from business or transfer to another role outside current BU or Function - no backfill

1


0

0

ELT-5




Not exiting

0


#REF!

#REF!






0


#REF!

#REF!

 

[Serenutty]

Thank you for your information about deleting particular cells, i need to dealing with spread sheet as part of my job. This post help me to understand stand more details. advantage of essay writing service

Thanks Diana.
I appreciate your words because I feel like a total incompetent by asking so many questions

 

[vletm]

Serenutty ...
I try to comment step-by-step:
there are errors e.g. it doesn't delete the right record when changing from "Exit ..." to "No exiting"
> Which would be the correct row to delete?
the formula in columns to the right are lost and error #REF! appears.
> those some formulas are lost because ...
( btw why there should be those formulas? )
... the code clears something from 'Costing'-sheet

To answer your (my) questions:
1. If on sheet named “Client” ... 2... 3
hmm... Which Your file has 'Client'-sheet?
3. ... those would be even correct if there could be something like You have written ...

I (You) will explain here how it works and what goes wrong:
hmm... 1...9 ... which file? and I would like to see those steps in file!

I changed to real names “Client” = “Pool” and “Decision” = “Costing” and “Yes” = “Exit from business or transfer to another role outside current BU or Function - no backfill”. It works but the same errors occurred:
It would be much easier FOR YOU to give sample files which no need to make 'own modifications there'...

Opened and closed spreadsheet, it ...
As written above ... why those formulas are there?

I hope you can shed some light ...
after those text, Your 'text' is ... unreadable ...
(did You check how do it look in Chandoo-forumn?)
maybe better 'looking' in other forum but ...

>>>>> Next >>>>>

Q1: What would be meaning of columns from M to Q with 'Pool'-sheet?
L-column is 'trigger' to copy or delete.

Q2: What would be meaning of 'Costing'-sheet?
What should be there?

Even this would be possible to do, but ...
I would need correct file,
even some samples written in that file
and
Your texts would match with that file, please.

 

[Serenutty]

Hi again vletm,

Thanks for persevering to help me with this problem.
I've attached the latest version of my file with correct names for every sheet, variable, formula etc.
The code works when changing from "Exit ..." to "No exiting". The problem is now with the formulas not being copied so I'm wondering if the solution would be to just copy those cells too.

I'll answer your questions but some I hoe you can just understand from the attached file.

1. > Which would be the correct row to delete? = I need to delete the row in sheet 2 "Costing" corresponding to a row in sheet 1 when "Exit..." changes to " No exiting". The code does that by adding date and time in column XX of both sheets 1 and 2

2. the formula in columns to the right are lost and error #REF! appears.
> those some formulas are lost because ... = Not sure why but I think that if those cells were also copied maybe the formulas would keep working

3. ( btw why there should be those formulas? ) = That's how the user has requested it.
4. ... the code clears something from 'Costing'-sheet
To answer your (my) questions:
1. If on sheet named “Client” ... 2... 3
hmm... Which Your file has 'Client'-sheet?
... those would be even correct if there could be something like You have written ... = Not sure about what you mean with these comments but I think once you look at the attached file you will understand.

5. I (You) will explain here how it works and what goes wrong:
hmm... 1...9 ... which file? and I would like to see those steps in file! = This is now sorted as it copies and deletes specified cells from each row properly

6. I changed to real names “Client” = “Pool” and “Decision” = “Costing” and “Yes” = “Exit from business or transfer to another role outside current BU or Function - no backfill”. It works but the same errors occurred:
It would be much easier FOR YOU to give sample files which no need to make 'own modifications there'... = I have now the attached file which will be final once the formulas are sorted. No need to change anything

7. I hope you can shed some light ...
after those text, Your 'text' is ... unreadable ...
(did You check how do it look in Chandoo-forumn?)
maybe better 'looking' in other forum but ... = I think I copied a spreadsheet and messed up the text in the message. Or it could be that it was too long

8. Q1: What would be meaning of columns from M to Q with 'Pool'-sheet? = This info will be manually entered by user

9. L-column is 'trigger' to copy or delete. = Both, it should copy when "Exit..." and delete when "No exiting" (bothe copy and delete on sheet 2

10, Q2: What would be meaning of 'Costing'-sheet?
What should be there? This info will be manually entered by user except the automatically copied cells triggered by L-column value

11. Even this would be possible to do, but ...
I would need correct file,
even some samples written in that file
and
Your texts would match with that file, please. = Done and attached final file with samples and even errors

Thank you so much for taking an interest in my problem, greatly appreciated. But I want to stress that another person in MrExcel forum is also looking at this. This is the link to that thread https://www.mrexcel.com/forum/excel...fic-cells-when-condition-no-longer-met-4.html
I hope I have explained clearly enough, very difficult for me to express things clearly
Thanks

 

[vletm]

Serenutty
... this time more terms match!
case 'Formulas'
a) I changed those M,O&P-columns formulas and now no matter of deletion
b) I also deleted a lot of unused M,O&P-columns formulas, don't worry!
c) Those formulas will create while selecting L-column value automatic

case 'code'
I did some 'minor changes' ... or of course there are something left.

some comments from Your answers:
8) mostly those are formulas ... I tried to as about meaning - okay
10) ... meaning ... okay
I tried to read those ...

Please, test again.

 

[Serenutty]

Hi vletm,

1. I've entered values in sheet 2 columns R and Y which should have been copied to sheet 1 columns P and O respectively to sheet 1 columns O and P. These is the formula for sheet 1 =Costing!Y2 and =Costing!R2
But it doesn't copy either value or formula.

2. The value copied does not match the row that it is in because in sheet 2 the order of rows is different than in sheet 1 because we change L value and rows are always added at the end of the list.

3.Column N formula is =IF(L2="Exit from business or transfer to another role outside current BU or Function - no backfill",1,0)
But all values appear as 1 regardless of L being "Exit ..." or "Not exiting" - so this is not working either

I hope you can see it in the new attachment

Thanks

 

[vletm]

> Formulas <
1) ex #10 Reply file's R2 is empty (P2 is 400)
There were one typo (there were left one fixed value, sorry)!
2) I didn't change that part ... would You explain with sample, what is wrong?
3) Ouch! another typo with formula, there were fixed '2' before row!
... and which would be 'cleared' to check
=if(L2="Not exiting",0,1) or
=if(L2="Exit from business or transfer to another role outside current BU or Function - no backfill",1,0)
... I would use SHORTER to check! ... much much much less text to check!
... and You didn't explain any specific meaning of L-column value!

As You wrote:
I hope you can see it in the new attachment


NOTE: I cleaned those looong formulas to shorter; now based with 'xx'-value.

 

[Serenutty]

OMG!!! You cracked it!!
It works perfectly.

Great idea making the formula as it goes along. I never thought of that. The spreadsheet looks cleaner and less prone to mistakes.

And searching for SHORTER text, duh!!

Typos! they are such a killer!! lol ... like my pun?
I did spot the fixed 2 before row but tried t correct and did not correct so I left it as it was :#

The only change I've made now is the correct column in sheet 2 to use row 16 sheet 1, highlighted and bold below.

[ If Target.Column = 12 Then
y = Target.Row
Cells(y, 13).Formula = "=if(L" & y & "=""Not exiting"",0,1)"
Cells(y, 15).Formula = "=SUMIF(Costing!XX:XX,Pool!XX" & y & ",Costing!Y:Y)"
Cells(y, 16).Formula = "=SUMIF(Costing!XX:XX,Pool!XX" & y & ",Costing!P)"]

Other than that it looks perfect.
Thank you so much for your help. You must be a genius!
Greatly appreciated.
I can now go to sleep in peace

PS- I just need to know how to give excellent feedback for you and close this thread as SOLVED

 

[Serenutty]

Hi,

My problem has been solved by two lovely people from two different forums.

The final code is this
[Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo M
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
CopyFrom = "Pool"
CopyTo = "Costing"
LookFor = "Not exiting"
Sheets(CopyFrom).Activate

If Target.Column = 12 Then
y = Target.Row
Cells(y, 13).Formula = "=if(L" & y & "=""Not exiting"",0,1)"
Cells(y, 15).Formula = "=SUMIF(Costing!XX:XX,Pool!XX" & y & ",Costing!Y:Y)"
Cells(y, 16).Formula = "=SUMIF(Costing!XX:XX,Pool!XX" & y & ",Costing!P)"
Lastrow = Sheets(CopyTo).Cells(Rows.Count, "A").End(xlUp).Row + 1
If Target.Value = LookFor Then
xx = Sheets(CopyFrom).Cells(y, "XX")
With Sheets(CopyTo)
For c = Lastrow To 2 Step -1
If .Cells(c, "XX") = xx Then
.Rows(c).Delete
c = -1
End If
Next c
End With
Else
With Sheets(CopyFrom)
.Cells(y, "XX") = Now()
.Range(.Cells(y, 1), .Cells(y, "H")).Copy Sheets(CopyTo).Cells(Lastrow, 1)
End With
Sheets(CopyTo).Cells(Lastrow, "XX") = Now()
End If
End If
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "Sorry we had some type problem. Try again"
End Sub]

Links to both forums:

https://mail.google.com/mail/u/0/#inbox/160cab5a7418f237

https://www.mrexcel.com/forum/excel...en-condition-no-longer-met-5.html#post4981640

https://www.excelforum.com/excel-pr...a-condition-is-no-longer-met.html#post4815652

https://chandoo.org/forum/threads/d...condition-is-no-longer-met.36879/#post-221415

Apologies for opening so many threads for the same problem but in hte end it was a team effort that got the solution so it was worth it, although I know not to do it again
You are all a great bunch, very generous with your time and knowledge. I appreciate it
Thank you all so much