Excel xp vba

Question

I wonder if I could trouble you with the followingvba problem:

I have got anspreadsheet in which a column, say column A, is populated with a large number of rows of URLs from different domains.

eg.

Column A

040FREIGHTMANAGEMENT.COM AGOREXSITEFACTORY.CO.uk showdown.tv slalander.se ARCHYSPLACE.COM vodka.ru DRINKWAREHOUSE.ir EASYRETAIL.COM IONWINE.au wine.fr LABARM.COM USWCOMMUNICATIONS.CO.uk easy.net etc, etc

I like to write a VBA macro which will delete the rows in which the URL does not end with a either a .com OR .co.uk.

While I can write a macro to batch delete rows ending with a specific domain (using the "find" command) I find this to be a somewhat laborious and unsatisfactory approach in view of the multitude of different domains present.

I cannot seem to find any examples on how to structure the desired coding from a trawl of the internet and I would be most grateful for any assistance you can give on the matter.

Many thanks Mian

Answer

Mian,

Sub Deleterows() Dim lastrow As Long, s As String, i As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = lastrow To 2 Step -1

s = Trim(Cells(i, "A"))
If LCase(Right(s, 4)) <> ".com" And LCase(Right(s, 6)) <> ".co.uk" Then
   Rows(i).Delete
End If

Next End Sub

Test it on a copy of your data since it deletes rows. Adjust it to the actual column you are using. Also, I assumed your data starts in row 2 and is contiguous down to the last data row.