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

John_356

New Member
Joined
Jan 17, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 17, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
@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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
=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.
Excel Formula:
=LEN(C6)
0 would mean it is really empty. Any other number would mean it is not.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 17, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Time2Learn

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Glad it helped.
 

Time2Learn

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?
VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,637
Members
417,159
Latest member
Mayozero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top