VBA delete range if criteria met - blank row bypass

Question

I want to delete a row within a range (not entire row) based on a criterion.
I am 90% there, however, the formatting of the data is holding me back somewhat.

The code below works well, it deletes the range ("I: Q") if the value in column "I" equals the value in cell "E2". However, there are blank rows within my data range (I: Q) that act as separators and therefore cannot be removed.

In the case of the first row being blank, the code stops and thinks it has finished its job. When actually it has done nothing.

Sub deleteb2()

    Dim FindRng         As Range
    Dim Rng1            As Range
    Dim LastRow         As Long
    Dim TexttoFind      As Integer
    Dim TexttoFind1     As String

    With Sheets("DN Compile")

    TexttoFind = .Range("E2").Value 
    later

        LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row '<-- get last row with 
        data in Column E

        Set Rng1 = .Range("I1:Q" & LastRow)

        Set FindRng = Rng1.Find(What:=TexttoFind, LookIn:=xlValues, 
        LookAt:=xlWhole)

        While Not FindRng Is Nothing '<-- find was successful

            FindRng.Resize(, 10).delete xlShiftUp '<-- delete column "I:Q" in 
            found row

            Set FindRng = Rng1.Find(What:=TexttoFind, LookIn:=xlValues, 
            LookAt:=xlWhole)

        Wend
    End With
End Sub

My thinking was to somehow add an IF statement, that would cause the code to carry on looking, if for example, up to 5 consecutive blank rows are seen by the code and only then, it would stop looking further.

The code now works - I made one little adjustment and it seems to be working fine for me!

Here is what I changed:

LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row '<-- get last row with data in Column E

i.e I changed the last row line of code to count column I instead of column E.

Not sure if I should just delete the question, but thought since the code works it could be useful.

It's unclear whether you want to find *TexttoFind * within I:I or I:Q. — , Jan 07 '19 at 19:12
Have a look at this answer, and the thread. https://stackoverflow.com/a/40816145/4839827 — Ryan Wildry, Jan 07 '19 at 19:30
@user10862412, It shouldn't make a difference as far as I am aware. The *TexttoFind*, will be found in I:I, but searching for it in a larger range I:Q. Will not make any difference as far as working with the data that I am working with is concerned. However, I do see that what I have posted is slightly confusing. I will amend the post. — M.Laszkowski, Jan 08 '19 at 08:32
Thank you for your comments, I made one little adjustment and the code now works. However, it has thrown up a new problem, but I think I'll post a new question if I don't figure it out myself. The only thing I changed was the in the last row line of code. i.e I changed the code to count column I instead of column E. — M.Laszkowski, Jan 08 '19 at 09:44

score 2 · Accepted Answer · answered Jan 08 '19 at 02:13

Sub deleteb2()

Dim FindRng         As Range
Dim Rng1            As Range
Dim LastRow         As Long
Dim TexttoFind      As Integer
Dim TexttoFind1     As String

With Sheets("DN Compile")

TexttoFind = .Range("E2").Value


    LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row '<-- get last row with   data in Column E

    Set Rng1 = .Range("I1:Q" & LastRow)

    Set FindRng = Rng1.Find(What:=TexttoFind, LookIn:=xlValues, LookAt:=xlWhole)

    While Not FindRng Is Nothing '<-- find was successful

        .Range("i" & FindRng.Row).Resize(1, 10).Delete xlShiftUp '<-- Set range based on i column.

        Set FindRng = Rng1.Find(What:=TexttoFind, LookIn:=xlValues, LookAt:=xlWhole)

    Wend
End With
End Sub

The code works, but I realised in my original code what I was doing wrong. This works with that adjustment which I have already modified in the original post. Thanks for taking the time to answer. It is definitely appreciated! — M.Laszkowski, Jan 08 '19 at 09:57

VBA delete range if criteria met - blank row bypass

1 Answers1