Tech Support Guy banner
Status
Not open for further replies.

VBA Deleting row based on cell value

2K views 2 replies 2 participants last post by  Jimmy the Hand 
#1 ·
I have a fairly simple task. From Row 17 to row 51 and Row 96 to 163 , I want to delete the row if cells P and Q are empty.

From row 57 to row 94 I want to delete the row if cells B, C, P, and Q are empty

I'm not sure what I'm doing wrong here.

Sub CleanUp()

Dim endrow As Long
Dim x As Integer

Set endrow = Sheets("Work Order").Range("A17").End(xlUp).Row

For x = endrow To 17 Step -1
If Sheets("Work Order").Range("P" & x) = "" And Sheets("Work Order").Range("Q" & x) = "" Then Sheets("Work Order").Rows(x).EntireRow.delete
End If
Next x

Rows("167:180").Select
Selection.delete shift:=x1Up

End Sub
If you see something glaringly wrong or know a quicker way to do this, I would be very thankful.
 
#2 ·
Here's an example work sheet. If the code works right in the example, it would delete rows 4, 5, 9, and 10.

Code:
Dim endrow As Long
Dim x As Long

For x = endrow To 29 Step -1
If Sheets("Sheet1").Range("P" & x).Value = "" And Sheets("Sheet1").Range("Q" & x).Value = "" Then
Sheets("Sheet1").Rows(x).EntireRow.Delete
End If

Next x
Why won't it remove the rows?
 

Attachments

#3 ·
If you see something glaringly wrong or know a quicker way to do this, I would be very thankful.
Well, this one seems certainly wrong:
Code:
Set endrow = Sheets("Work Order").Range("A17").End(xlUp).Row
Should be:
Code:
Set endrow = Sheets("Work Order").Range("A17").End(xlDown).Row
Also, instead of
Code:
Selection.delete shift:=x1Up
this one looks better (note the digit "1" in x1Up replaced by the letter "L"):
Code:
Selection.delete shift:=xlUp
Although it's actually useless in our case, because entire rows are deleted.

And here's a quicker way (no loops):
Code:
Sub CleanUp()
    Dim Rng As Range
    
    Set Rng = Intersect(Range("96:163"), _
        Range("P:P").SpecialCells(xlCellTypeBlanks).EntireRow, _
        Range("Q:Q").SpecialCells(xlCellTypeBlanks).EntireRow)
    If Not Rng Is Nothing Then Rng.Delete
    
    Set Rng = Intersect(Range("57:94"), _
        Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow, _
        Range("C:C").SpecialCells(xlCellTypeBlanks).EntireRow, _
        Range("P:P").SpecialCells(xlCellTypeBlanks).EntireRow, _
        Range("Q:Q").SpecialCells(xlCellTypeBlanks).EntireRow)
    If Not Rng Is Nothing Then Rng.Delete
    
    Set Rng = Intersect(Range("17:51"), _
        Range("P:P").SpecialCells(xlCellTypeBlanks).EntireRow, _
        Range("Q:Q").SpecialCells(xlCellTypeBlanks).EntireRow)
    If Not Rng Is Nothing Then Rng.Delete
End Sub
I think the code is self-explanatory, but should you have questions, ask anyway.

Jimmy
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top