Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - delete row if cell contains string
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - delete row if cell contains string
thats right tony,
you got it! Alex |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro syntax to check string length in a cell | Excel Discussion (Misc queries) | |||
How can delete the duplicate word from a string in cell in EXCEL | Excel Discussion (Misc queries) | |||
How do I delete part of a text string in every cell it appears in | Excel Discussion (Misc queries) | |||
A Macro to replace a string in a cell with a string from another cell??? | Excel Programming | |||
Macro to delete last charcter in a text string | Excel Programming |