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

Question

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

after

In general you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. When deleting lines like that you need to start at the bottom: `For s = 22 To 2 step -1` — cybernetic.nomad, Jul 10 '19 at 21:44
how can I delete the cells without using select? also starting from the bottom cause excel hanging and should be restarted — adnan, Jul 10 '19 at 21:47

cybernetic.nomad · Accepted Answer · 2019-07-10T22:09:32.217

2

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:

edited Jul 10 '19 at 22:09

answered Jul 10 '19 at 21:53

cybernetic.nomad

6,100
3
18
31

1

I edited to match the sheet names :). Okay I actually made a few edits just in case :)) – dwirony Jul 10 '19 at 21:54
Yes, Right, sorry for asking about select quickly without think about it, about step backwards I don't know why if I do that the excel keep searching and searching with no end, so I should restart the excel itself before the code ends (it keeps searching on row 22 – adnan Jul 10 '19 at 21:59
Ok, now after added '.Value' the crashing issue solved, but still deleting only the half results if the empty cells under each others, kindly recheck the attached screens – adnan Jul 10 '19 at 22:02
I'm not sure if I understand what you mean. Could you maybe edit your question and show us a screencap of the desired result? – cybernetic.nomad Jul 10 '19 at 22:03
photos attached already, for example in first photo the range that should be delted is (G5:H5) and (G6:H6) , but the delted is only one of them after execute the code – adnan Jul 10 '19 at 22:07
solved after change s from Integer to Long, and moving backwards, and avoid select , many thanks to your Awesome help – adnan Jul 10 '19 at 22:19
1

FYI, Excel allows for more rows than `Integer` can handle. as a rule, you should always use `Long` since `Integer` has no benefit over it – cybernetic.nomad Jul 10 '19 at 22:26

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

1 Answers1