Deleting cells based on cell value of samerow

Solved/Closed
Trowa - May 17, 2010 at 09:36 AM
 trowa - May 25, 2010 at 08:34 AM
Hello,

When I delete cell D4 (it's contents turn to ""), I would like cells C4, E4, F4, G4 to be deleted as well.
The row must still exist, so only clear it's contents.
The target row can be anything between 4 and 1040.
How do I change the 4 from cell D4 in something like active.row?

Best regards,
Trowa


1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 17, 2010 at 09:56 AM
Use the sheet event
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
0
Hi Rizvisa,

By your answer I figured that you thought I should be able to come with the answer myself. So I looked throught the codes you have given me and found the loop code I discarded before. I have adjusted it and it works fine. For those reading this post and like to see the code here it is:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    'if you want to find out the last used cell in column M
    llastrow = Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

    'in case you want to hard code
    'llastrow = 1040

    processrow = 4

    Do While (processrow <= llastrow)

        If (Cells(processrow, "D") = "") Then

            Cells(processrow, "C").ClearContents
            Cells(processrow, "E").ClearContents
            Cells(processrow, "F").ClearContents
            Cells(processrow, "G").ClearContents
            
        End If

    processrow = processrow + 1

    Loop
    Application.EnableEvents = True

End Sub


Thanks and best regards,
Trowa
0
I have to come back on the working fine part!
After saving my workbook it's not working anymore!
I only added column A to be cleared as well, can you have a look at my workbook?
https://authentification.site/files/22535057/Copy_of_PostPlanning.xls
The code is for sheet "Alle opdrachten"

Thanks in advance,
Trowa
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 20, 2010 at 09:39 AM
LOL, not at all Trowa. Unlike most guys here, I know that you only look for a hint about the solution. I was not trying to do any thing of that sort. Next time I will answer full to my ability

Looking at your solution, just a suggestion

Cells(processrow, "C").ClearContents
Cells(processrow, "E").ClearContents
Cells(processrow, "F").ClearContents
Cells(processrow, "G").ClearContents

can be consolidated into

Range(Cells(processrow, "C"), Cells(processrow, "G")).ClearContents

D is included in range but it will not matter as you have already disabled the events at the top.

I am also a bit confused over need for a do loop

Would not this be a shade better ?

Private Sub Worksheet_Change(ByVal Target As Range) 

    Application.EnableEvents = False 

    'if you want to find out the last used cell in column M 
    llastrow = Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row 

    'in case you want to hard code 
    'llastrow = 1040 

    processrow = 4 

    For Each Cell In Target 
         
        If Cell.Row < processrow Then GoTo Next_Cell ' only we need to work if row is row 4, 5, and up 
        If Cell.Column <> 4 Then GoTo Next_Cell 'only we need to work if column is D 
        If Cell <> "" Then GoTo Next_Cell ' only we need to work if cell value in D is  "" 
         
        'define the range from one column to left till 3 columns to right of current cell 
        Range(Cell.Offset(0, -1), Cell.Offset(0, 3)).ClearContents 
         
Next_Cell: 
    Next Cell 
     
    Application.EnableEvents = True 

End Sub
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 20, 2010 at 09:49 AM
Trowa
It works for me. though it is slow coz of do loop. It is processing rows that are not impacted by change. I think my solution would be a better fit here. You would need add A to my code as a separate line, as you dont want to clear B

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    'if you want to find out the last used cell in column M
    llastrow = Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

    'in case you want to hard code
    'llastrow = 1040

    processrow = 4

    For Each CELL In Target
         
        If CELL.Row < processrow Then GoTo Next_Cell ' only we need to work if row is row 4, 5, and up
        If CELL.Column <> 4 Then GoTo Next_Cell 'only we need to work if column is D
        If CELL <> "" Then GoTo Next_Cell ' only we need to work if cell value in D is  ""
         
        'define the range from one column to left till 3 columns to right of current cell
        Range(CELL.Offset(0, -1), CELL.Offset(0, 3)).ClearContents
        CELL.Offset(0, -3).ClearContents
Next_Cell:
    Next CELL
     
    Application.EnableEvents = True

End Sub
0
I agree, Rizvisa, this code works instant and does exactly what I want. I'm just a bit lost at why the code works.
Don't you have to define the words "CELL" and "Target"? How does Excel know what to do with the lines "For Each CELL In Target" and "Next CELL"?
"Target" is a range according to the first codeline, but we didn't tell Excel what range, right?

Anyway, thanks for another great code.
Best regards,
Trowa
0