98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Deleting rows with a O
Deleting rows with a O
Resolved · Urgent Priority · Version 2007
David has attended:
Excel VBA Intro Intermediate course
Deleting rows with a O
Hi, I've been using the code below to remove rows based on specific value of the target column. It works fine until I try and use the code to remove the row's where the value is 0. Rather than picking up just those with a 0, it removes anything with a 0 in eg 50126. Please can you advise?
Private Sub DeleteRows(ColumnLetter As String, DataItem As String, RowNumber As Integer)
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range(ColumnLetter & "1", ColumnLetter & RowNumber)
Do
Set c = SrchRng.Find(DataItem, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
RE: Deleting rows with a O
Hi David
Try editing the line which carries out the search to:
Set c = SrchRng.Find(DataItem, LookIn:=xlValues, LookAt:=xlWhole)
(in one line)
Your code will then only find and delete rows where the whole cell is 0.
If instead you put
LookAt:=xlPart
then any value containing a 0 will be found and deleted.
If the LookAt is missed out Excel chooses the current value in 'Match entire contents' option in the Find dialog box.
Please let me know if the suggestion works, thanks.
Doug Dunn
Best STL
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Adding up time greater than 24 hoursWhen you add up time if it exceeds 24 hours i.e 27 hours appears as 03:00. Go to Format / Cells / Number / Custom. The format is hh:mm but if change it to [hh]:mm it will add up to the correct amount of hours. |