Hi,

I have a worksheet in excel which has lots of rows and three columns
Column A and B have data that is relevant to the list, but column C ha
number "17" appearing on every 17th row. What I want my code to do i
delete every row that has 17 appearing in column C. I have managed t
put this code together, but I cannot make up the crucial bit of it:

With Worksheets(1).Range("C1:C10500")
Set c = .Find(17, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
XXXXXXXXXXXXXXX
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

The XXXXXXXXXXXXXXX above is the missing bit. Please help me out.

Thanks in advance.

Ana

--
anar_bak
-----------------------------------------------------------------------
anar_baku's Profile: http://www.excelforum.com/member.php...fo&userid=1825
View this thread: http://www.excelforum.com/showthread.php?threadid=50993

Hi Anar,

The following macro doesn't use a loop as you have shown below & I
think it may be quicker to execute.
I have used a datafilter to show the "17"'s, select visible rows within
the datarange (+1 row because I offset to prevent the headers being
deleted), & delete them, then removes the autofilter:


Code:
--------------------
Sub DeleteSeventeens ()
Dim FilterRange As String 'line 1, place at start of code
FilterRange = "A1:C10500" 'line 2
Range(FilterRange).AutoFilter Field:=3, Criteria1:="=17", Operator:=xlAnd 'line 3
Range(FilterRange).Offset(1, 0).EntireRow.SpecialCells(xlCellTypeVisible).Delet e Shift:=xlUp 'line 4
Selection.AutoFilter 'line 5
end sub
--------------------


In case my post messes up where the line breaks should be, I have put
them in as comments on the end of each line. NB, the FilterRange can be
changed as required (& currently this macro must be on the same sheet)
- I have declared it as a string but someone may suggest a way of
declaring this as a range.

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=509939

Sub DeleteRows()
With Worksheets(1).Columns(3)
Set c = .Find(17, lookin:=xlValues)
If Not c Is Nothing Then
Do
c.EntireRow.Delete
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub

--
Regards,
Tom Ogilvy




"anar_baku" wrote
in message ...

Hi,

I have a worksheet in excel which has lots of rows and three columns.
Column A and B have data that is relevant to the list, but column C has
number "17" appearing on every 17th row. What I want my code to do is
delete every row that has 17 appearing in column C. I have managed to
put this code together, but I cannot make up the crucial bit of it:

With Worksheets(1).Range("C1:C10500")
Set c = .Find(17, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
XXXXXXXXXXXXXXX
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

The XXXXXXXXXXXXXXX above is the missing bit. Please help me out.

Thanks in advance.

Anar


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile:
http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=509939