CODEWITHSUNDEEP
excelvba
adnan
adnan

Reputation: 123

Delete cells depend on thier values works fine but skipped the half

I need to delete 2 or more (variable depending on work) cells in the same row starting from row 2 if the 2 cells are = ""

I used this code and it's already working fine except 1 problem

Sub Macro3()
    Dim s As Integer
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet2")

    For s = 2 To 22
        If ws.Range("G" & (s)) = "" And ws.Range("H" & (s)) = "" Then
            Union(ws.Range("G" & s), ws.Range("H" & s)).Select
            Selection.Delete Shift:=xlUp
        End If
    Next s
End Sub

the problem is if I have for example from G2:H4 (2rows or more achieve the if condition) it's only delete half of them,

if 5 rows delete 3 only...etc

so I think the loop doesn't operate on the current cell (just guessing)

Attach screens is before and after running the code for more clarification

before

before

after

after

Upvotes: 0

Views: 33

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6418

Sub Macro3()

Dim s As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet2")

For s = 22 To 2 Step -1

    If ws.Range("G" & s).Value = "" And ws.Range("H" & s).Value = "" Then 
        ws.Rows(s).Delete Shift:=xlUp
        'or:
        'ws.Range("G" & s & ":H" & s).Delete Shift:=xlUp
    End if

Next s

End Sub

After running the code:
enter image description here

Upvotes: 2

Related Questions