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
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
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.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!