Selecting Rows

[anar_baku]

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

[broro183]

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:

Please Login or Register  to view this content.

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

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>