VBA my example needs to Delete data in column F if met empty cells on G (Almost done)

[lilloscar]

My code is limited to work fine only with first empty cell found, the problem starts is that if finds the next two or more empty cells because it loops a bit (I can handle with that), but if it finds empty cells and next finds cells with data again, it totally fails.

Private Sub Worksheet_Change(ByVal Target As Range)

    firstRow = 7
    lastrow = Sheets("Datos del Proyecto").Range("F" & Rows.Count).End(xlUp).row
    i = firstRow
    Do Until i > lastrow
        If Sheets("Datos del Proyecto").Range("G" & i).Value Like "" Then
            Sheets("Datos del Proyecto").Range("F" & i).ClearContents
        End If
        i = i + 1
    Loop

End Sub

Screenshot

I've been searching a lot to fix it by myself, but I really need now your help guys, thanks.

 

[My Aswer Is This]

This script is saying any time any thing changes on this sheet you want this script to run.

I would think this should be a Module script and run when you click a button or use a shortcut key.
Not used as a sheet change event script.

 

[lilloscar]

when there is data in row "TRABAJADOR 5" it only loops, so I need to press Esc twice, then it shows that the problem is in the line with the code "End If"

End If

 

[lilloscar]

This script is saying any time any thing changes on this sheet you want this script to run.

I would think this should be a Module script and run when you click a button or use a shortcut key.
Not used as a sheet change event script.

Hi!, yes it runs every time I edit a cell in this Worksheet, it can be possible to work OK with the first examples said, does you think that this

<code>Worksheet_Change(ByVal Target As Range)</code>

Limitate all the code? I made this way because I need to clear contents automatically because other people with this excel file won't will do it.

 

[Rick Rothstein]

ADVERTISEMENT

My code is limited to work fine only with first empty cell found, the problem starts is that if finds the next two or more empty cells because it loops a bit (I can handle with that), but if it finds empty cells and next finds cells with data again, it totally fails.

Private Sub Worksheet_Change(ByVal Target As Range)

    firstRow = 7
    lastrow = Sheets("Datos del Proyecto").Range("F" & Rows.Count).End(xlUp).row
    i = firstRow
    Do Until i > lastrow
        If Sheets("Datos del Proyecto").Range("G" & i).Value Like "" Then
            Sheets("Datos del Proyecto").Range("F" & i).ClearContents
        End If
        i = i + 1
    Loop

End Sub

Your code is doing far more work than is necessary for a Change event procedure... the beauty of Change event code is it allows you to focus the code on only those cells that changed (rather than continually looking at cells that have not changed). Also, since the Change event is locked to a worksheet, there is no need to reference the worksheet as it must be the active sheet (so all unspecified range references default to that active sheet). One question, though, before we can give you revised code... using Row 9 as an example, obviously if F9 is cleared, G9 should be cleared, but about if F9 is empty and the user types something in G9... should it be deleted immediately?

 

[lilloscar]

Your code is doing far more work than is necessary for a Change event procedure... the beauty of Change event code is it allows you to focus the code on only those cells that changed (rather than continually looking at cells that have not changed). Also, since the Change event is locked to a worksheet, there is no need to reference the worksheet as it must be the active sheet (so all unspecified range references default to that active sheet). One question, though, before we can give you revised code... using Row 9 as an example, obviously if F9 is cleared, G9 should be cleared, but about if F9 is empty and the user types something in G9... should it be deleted immediately?

Hello Rick! thanks, yes, in resume if there is no data on G there shouldn't on F so it should be deleted immediately, the only thing needed to don't go to a loop is putting on the code this:

<code>Application.EnableEvents = False
<code>Application.EnableEvents = True</code></code>

I achieve this in two ways:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long, i As Long

Application.EnableEvents = False
LastRow = Cells(Rows.Count, "F").End(xlUp).row

For i = 7 To LastRow
    If IsEmpty(Range("G" & i)) Or Range("G" & i).Value = "" Then
        Range("F" & i).ClearContents
    End If
Next i

Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Application.EnableEvents = False
    firstRow = 7
    lastrow = Sheets("Datos del Proyecto").Range("F" & Rows.Count).End(xlUp).row
    i = firstRow
    Do Until i > lastrow
        If Sheets("Datos del Proyecto").Range("G" & i).Value Like "" Then
            Sheets("Datos del Proyecto").Range("F" & i).ClearContents
        End If
        i = i + 1
    Loop
Application.EnableEvents = True
End Sub

If I edit the last row on F, it gets a little loop on both of them, but I can handle that I think so.

 

[Rick Rothstein]

Hello Rick! thanks, yes, in resume if there is no data on G there shouldn't on F so it should be deleted immediately

Assuming you only fill in one cell at a time (no copy/pasting multiple cells into either Column F or G), then this event code should do what you want...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Intersect(Target.EntireRow, Columns("G")) = "" Then Intersect(Target.EntireRow, Columns("F")).ClearContents
End Sub[/td]
[/tr]
[/table]

 

[lilloscar]

Assuming you only fill in one cell at a time (no copy/pasting multiple cells into either Column F or G), then this event code should do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Intersect(Target.EntireRow, Columns("G")) = "" Then Intersect(Target.EntireRow, Columns("F")).ClearContents
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Works! Really appreciate your answer Rick, this is easier way without much code, cheers!