Delete rows from botton up within range if both column C and D cells are blank

[John_356]

Good day,

Provided LRR has been successfully defined as last used row,

I've been trying to find a way to delete all rows within my dynamic range (Range ("C2:D" & LRR)) when both cells from same row on columns C and D are blank.

I'm looking for a short and elegant code here

Many thanks in advance,

John

 

[John_356]

@Fluff ,

It worked, thank you!

May I ask you what was the issue? I see your additional code:

With Intersect(ActiveSheet.UsedRange, Range("C:D"))
.Value = .Value

That changed how VBA perceived NONTEXT elements. Do you think I can use the same syntax anytime this issue happens?

In any case, thank you very much once again for solving this. Thank you both @Joe4 for your swift replies!

Cheers

 

[Joe4]

=CODE(C6) formula I get #VALUE!

Remember, you need to replace the "C6" in that formula with a cell that is "blank" on your sheet.

What that formula returns is the ASCII code of the first character of the entry. Typical spaces are "32".
"A" is 97. "a" is 65.

If you are getting the #VALUE error, then it sounds like that cell really is blank. So do the same thing to the column D entry on the same row (that should be blank).

You can also use the LEN function to see if there is anything really in the cell, i.e.

=LEN(C6)

0 would mean it is really empty. Any other number would mean it is not.

 

[Joe4]

Glad to see that you got it working, though I would love to know what was going on.
The techniques I proposed can help you identify that, if you are interested.

 

[Fluff]

May I ask you what was the issue?

I suspect that you had copied & pasted the data which originally had formulae.
If you paste as values & a formula is returning "" then you will get a null string in those cells.
However if you use .Value=.Value instead of getting the null string you will get a blank cell.

 

[John_356]

I suspect that you had copied & pasted the data which originally had formulae.

Indeed, this table is the copy/special paste value result of two pivot tables created for this purpose and deleted after use.

Thanks again, this has been very educational!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Time2Learn]

Try

Sub John()
   With Intersect(ActiveSheet.UsedRange, Range("C:D"))
      .Value = .Value
   End With
   On Error Resume Next
   Intersect(Range("C:C").SpecialCells(xlBlanks).EntireRow, Range("D:D").SpecialCells(xlBlanks).EntireRow).Delete
   On Error GoTo 0
End Sub

THANK YOU! this solves my exact issue as well.

 

[Fluff]

Glad it helped.

 

[Time2Learn]

Glad it helped.

I have another scenario where I'm looking to delete entire rows if instead of blank C:D if value of A="." and value of B="." i've tried using this as a starting point but I jsut keep messing it up by trying things like Range("A:A").Value etc.... any advice?

 

[Time2Learn]

I have another scenario where I'm looking to delete entire rows if instead of blank C:D if value of A="." and value of B="." i've tried using this as a starting point but I jsut keep messing it up by trying things like Range("A:A").Value etc.... any advice?

I've got the following, but is there a more elegant solution?

    Dim i As Long, N As Long

    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = N To 1 Step -1
        If Cells(i, "A").Value = "." And Cells(i, "B").Value = "." Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next i