• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Excel 2007 Code to Delete Rows If Specific Cell Has Content

Hello,

Can someone provide a code that starts on row 2 of sheet1 and if there is any content/text in cell Q2 the entire row is deleted and then loops down to the next row?  This would continue to loop until there is no more content in column A.

Thanks!
0
Escanaba
Asked:
Escanaba
  • 3
  • 2
1 Solution
 
ragnarok89Commented:
Here you go:

sub x()

r=2
while range("A" & r).value <> ""
   if range("Q" & r).value = "" then rows(r).delete
wend

end sub

Open in new window

0
 
ragnarok89Commented:
sorry, I missed a bit. this works
Sub x()

r = 2
While Range("A" & r).Value <> ""
    If Range("Q" & r).Value = "" Then
        Rows(r).Delete
    Else
        r = r + 1
    End If
Wend

End Sub

Open in new window

0
 
ragnarok89Commented:
Use

 If Range("Q" & r).Value <> "" Then

to delete rows when col Q is not blank.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dlmilleCommented:
How about doing it without a loop?  How about 2 steps - setup a filter, and delete it!

App steps are:  Clear filters, set a filter based on row 2 to the lastrow based on data in column A.  Then, filter column Q having data and then delete what's being filtered.

Here's the code - works on the active sheet.

 
Sub deleteRows()
Dim filterRng As Range
Dim lastRow As Long

    ActiveSheet.AutoFilterMode = False 'turn off any active filtering
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row 'stop when you hit the bottom of the dataset based on column A
    
    Set filterRng = Range("A2", Range("Q" & lastRow))

    filterRng.AutoFilter field:=Columns("Q").Column, Criteria1:="<>" 'now filter column Q having Data
    
    filterRng.EntireRow.Delete
    
    
End Sub

Open in new window



See attached demonstration workbook.  Note, I took you at your word, so the last row to be considered is based on column A, while other columns go lower than column A.  Likely, your last item in Column A is the same last item row for the dataset, but I set up an example that didn't assume that, lol.

Cheers,

Dave
deleteFilteredColQDriver-r1.xlsm
0
 
EscanabaAuthor Commented:
Thank you!
0
 
dlmilleCommented:
@escabana - any issues with the solution I posted (just curious if you tried it, no worries about points).

Cheers,

Dave
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now