Delete rows based on the value of a cell

Hi all,TLC test.xlsm

Please I need help to complete my work, I have a code that makes a button to add cells (rows) under a reference cell, and I need to find a way to delete the added cells (rows) and keep only the rows above the reference cell if I change the value of another cell “key cell”. I attached a simulation of the file and fully described what I need help with.Thanks in advance


Emma_B

Hi Emma,
try it (attached)

Thanks nilem, I got an error with the file you sent, this happen when I change the value in cell C4! I integrated the code you wrote with the original one so that it works to creat new addresses with new letters . However the main problem does exist (deleting all addresses (rows) after address C in case I cahnged the value of the cell C4. I rewrote what I need in a different way so hope it will be clear now. see that attached file.

Moreover, I would like to ask you to explain the this piece of code for me?

Please Login or Register  to view this content.

is'nt it the code for giving new letters to the neww addresses?


Regards
Emma_B

THe file attached again

TLC test1.xlsm

"is'nt it the code for giving new letters to the neww addresses?"
Yeah, right

try again

Fantastic, thats and epic solution thanks a lot. I still need you to explain this piece of code in words for me because I want to apply in in other files that have other number of cells and rows and letters. i.e. the last address might not be C it could be K. I appreciate if you can write how to I read/interpret this code (specially the Chr part).

Please Login or Register  to view this content.

Regards
Emma_B

I just tried to bind row number of the range "New_address" (i = [New_address].Row) and a character code (a number that represents a particular character in a set, such as the ANSI character set).
Try it to understand better

Please Login or Register  to view this content.

Hi,

Sorry again I could not ask a clear question . I understand the character code i.e what number represents which character, what I wanted to know is how do you address the target cell and tell the code to go and look at the letter there and produce the next letter in the alphabet in the new cell (in the new created address). in other words how do you calculate the "distance" number of rows & columns between the the cell that has the letter and the one that shall have the next letter?. what is the (/ 6 + 16) in the code?

/Emma_B

ok, I'll try to explain
if row number of the range "New_address" is 28, we should get 68 (Chr(68) returns "D")
if row number of the range "New_address" is 34, we should get 69 (Chr(69) returns "E")
if row number of the range "New_address" is 40, we should get 70 (Chr(70) returns "F")
etc.
and I came up with a formula for this sequence of numbers
(28-16)/6+66=68
(34-16)/6+66=69
(40-16)/6+66=70
(34-16)/6+66=69
and so on

Sorry, my English is not the best

That is brilliant solution Thank you very much