deleting rows o

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Deleting rows with a O

Deleting rows with a O

resolvedResolved · 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


Edited on Wed 28 Nov 2012, 16:21

RE: Deleting rows with a O

Hi Doug,

Thanks for your advice. That all worked fine and you've allowed me to complete a big piece of work for a client ahead of schedule. Really appreciate your help

Thanks again

Dave

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Adding up time greater than 24 hours

When 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.

View all Excel hints and tips


Server loaded in 0.08 secs.