macro - delete row if cell contains string
 

Hi all,

Can some one help me please!
Here is a slightly more complex macro that I really need. I have a list
of domain names in excel for example:

1 www.throneofnails.com
2 www.throneofnails.com/info/index.html
3 www.fairkess.com/en/business/industrie.html
4 www.endlessdecor.com/Links.html
5 www.retractableawnings.com/resources.htm
6 www.ffbd-tm.com
7 www.ffbd-tm.com/test

I need a macro that will delete all the top level domain names eg
www.throneofnails.com and delete all files in that domain eg
www.throneofnails.com/info/index.html.

I need to be left with a list of only domain names like domain name 3,4
and 5.

I think it will probably have something to do with finding the cells
that dont contain a "/" then loading them into an array then deleting
all cells that contain the contents of that array.

Unfortunately I dont have the programing language to do this.

Your help would be much appreciated.

Many thanks

Alex Kemsley

macro - delete row if cell contains string
 

You could use a helper column with formulas like:

=isnumber(search("/",a2))

Then drag it down the column.

Then apply data|filter|autofilter to that column.
Show the Trues and delete those visible rows.

wrote:

Hi all,

Can some one help me please!
Here is a slightly more complex macro that I really need. I have a list
of domain names in excel for example:

1 www.throneofnails.com
2 www.throneofnails.com/info/index.html
3 www.fairkess.com/en/business/industrie.html
4 www.endlessdecor.com/Links.html
5 www.retractableawnings.com/resources.htm
6 www.ffbd-tm.com
7 www.ffbd-tm.com/test

I need a macro that will delete all the top level domain names eg
www.throneofnails.com and delete all files in that domain eg
www.throneofnails.com/info/index.html.

I need to be left with a list of only domain names like domain name 3,4
and 5.

I think it will probably have something to do with finding the cells
that dont contain a "/" then loading them into an array then deleting
all cells that contain the contents of that array.

Unfortunately I dont have the programing language to do this.

Your help would be much appreciated.

Many thanks

Alex Kemsley

--

Dave Peterson

macro - delete row if cell contains string
 

Sub hij()
Dim i As Long, j As Long, s As String
i = 1
Do While Not IsEmpty(Cells(i, 1))
If InStr(1, Cells(i, 1), "/", vbTextCompare) = 0 Then
s = Cells(i, 1)
j = i + 1
Do While InStr(1, Cells(j, 1), s, vbTextCompare) < 0
j = j + 1
Loop
Range(Cells(i, 1), Cells(j - 1, 1)).EntireRow.Delete
Else
i = i + 1
End If
Loop
End Sub

worked for me with your data.

--
Regards,
Tom Ogilvy



" wrote:

Hi all,

Can some one help me please!
Here is a slightly more complex macro that I really need. I have a list
of domain names in excel for example:

1 www.throneofnails.com
2 www.throneofnails.com/info/index.html
3 www.fairkess.com/en/business/industrie.html
4 www.endlessdecor.com/Links.html
5 www.retractableawnings.com/resources.htm
6 www.ffbd-tm.com
7 www.ffbd-tm.com/test

I need a macro that will delete all the top level domain names eg
www.throneofnails.com and delete all files in that domain eg
www.throneofnails.com/info/index.html.

I need to be left with a list of only domain names like domain name 3,4
and 5.

I think it will probably have something to do with finding the cells
that dont contain a "/" then loading them into an array then deleting
all cells that contain the contents of that array.

Unfortunately I dont have the programing language to do this.

Your help would be much appreciated.

Many thanks

Alex Kemsley

macro - delete row if cell contains string
 

You can try something similar to this:
Dim rng As Range
Columns("A:A").Select
For Each rng In Selection
rng.Activate
If ActiveCell.Value < "" And InStr(1, ActiveCell.Value, "/") = 0 Then
ActiveCell.Delete (xlShiftUp)
ActiveCell.Offset(1, 0).Activate
End If
Next

" wrote:

Hi all,

Can some one help me please!
Here is a slightly more complex macro that I really need. I have a list
of domain names in excel for example:

1 www.throneofnails.com
2 www.throneofnails.com/info/index.html
3 www.fairkess.com/en/business/industrie.html
4 www.endlessdecor.com/Links.html
5 www.retractableawnings.com/resources.htm
6 www.ffbd-tm.com
7 www.ffbd-tm.com/test

I need a macro that will delete all the top level domain names eg
www.throneofnails.com and delete all files in that domain eg
www.throneofnails.com/info/index.html.

I need to be left with a list of only domain names like domain name 3,4
and 5.

I think it will probably have something to do with finding the cells
that dont contain a "/" then loading them into an array then deleting
all cells that contain the contents of that array.

Unfortunately I dont have the programing language to do this.

Your help would be much appreciated.

Many thanks

Alex Kemsley

macro - delete row if cell contains string
 

Thanks dave,
It works great for the top level domains,
But I need to be able to delete the files of the domains it deletes
too.
Any Idea how i can do that too?
Alex

macro - delete row if cell contains string
 

Sub hij()
Dim i As Long, j As Long, s As String
i = 1
Do While Not IsEmpty(Cells(i, 1))
If InStr(1, Cells(i, 1), "/", vbTextCompare) = 0 Then
s = Cells(i, 1)
j = i + 1
Do While InStr(1, Cells(j, 1), s, vbTextCompare) < 0
j = j + 1
Loop
Range(Cells(i, 1), Cells(j - 1, 1)).EntireRow.Delete
Else
i = i + 1
End If
Loop
End Sub

worked for me with your data.

--
Regards,
Tom Ogilvy



" wrote:

Hi all,

Can some one help me please!
Here is a slightly more complex macro that I really need. I have a list
of domain names in excel for example:

1 www.throneofnails.com
2 www.throneofnails.com/info/index.html
3 www.fairkess.com/en/business/industrie.html
4 www.endlessdecor.com/Links.html
5 www.retractableawnings.com/resources.htm
6 www.ffbd-tm.com
7 www.ffbd-tm.com/test

I need a macro that will delete all the top level domain names eg
www.throneofnails.com and delete all files in that domain eg
www.throneofnails.com/info/index.html.

I need to be left with a list of only domain names like domain name 3,4
and 5.

I think it will probably have something to do with finding the cells
that dont contain a "/" then loading them into an array then deleting
all cells that contain the contents of that array.

Unfortunately I dont have the programing language to do this.

Your help would be much appreciated.

Many thanks

Alex Kemsley

macro - delete row if cell contains string
 

sorry jim,
I dont seem to be able to get that to work, it deleted about 10 of them
but left the rest, i cant seem to work out why.
alex

macro - delete row if cell contains string
 

the data is availible online to view in an xls at
http://www.hottubs2buy.co.uk/10.xls
thanks

macro - delete row if cell contains string
 

How do you read it?

a
a/b
c/d
e
e/f

delete everything but c/d as I understood it.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote:

You could use a helper column with formulas like:

=isnumber(search("/",a2))

Then drag it down the column.

Then apply data|filter|autofilter to that column.
Show the Trues and delete those visible rows.

wrote:

Hi all,

Can some one help me please!
Here is a slightly more complex macro that I really need. I have a list
of domain names in excel for example:

1 www.throneofnails.com
2 www.throneofnails.com/info/index.html
3 www.fairkess.com/en/business/industrie.html
4 www.endlessdecor.com/Links.html
5 www.retractableawnings.com/resources.htm
6 www.ffbd-tm.com
7 www.ffbd-tm.com/test

I need a macro that will delete all the top level domain names eg
www.throneofnails.com and delete all files in that domain eg
www.throneofnails.com/info/index.html.

I need to be left with a list of only domain names like domain name 3,4
and 5.

I think it will probably have something to do with finding the cells
that dont contain a "/" then loading them into an array then deleting
all cells that contain the contents of that array.

Unfortunately I dont have the programing language to do this.

Your help would be much appreciated.

Many thanks

Alex Kemsley

--

Dave Peterson

macro - delete row if cell contains string
 

thats right tony,
you got it!
Alex

macro - delete row if cell contains string
 

I posted a tested macro that does exactly that, but I guess you don't want to
use it.

--
Regards,
Tom Ogilvy



" wrote:

thats right tony,
you got it!
Alex

macro - delete row if cell contains string
 

Looks like a ploy to open a web page of advertising set up to look like a
404 error.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
the data is availible online to view in an xls at
http://www.hottubs2buy.co.uk/10.xls
thanks