VBA: How can I delete specific ROWS and then remove remaining dots and dashes?

[NeverHappyMike]

Hi


I've recently been handed an Excel file to format - 100's of lines. This could be a really long exercise if I work through it manually, so I wondered if a macro could help.


My job is too:


1. Delete the rows where Dot&Dot&Dash are used at the beginning and together. Then;

2.Go through the list again and remove the remaining Dots&Dash characters without deleting the rows.

I'm hoping that this should then leave my new formatted list aligned to the left.


Anyway if any of you can point me to the correct things to put into a suitable Macro then it would be really appreciated (and save me hours of work, as I feel this isn't the only sheet they have formatted in this way).


Many thanks


Mike.


Here's a link to an image of a little bit of the fiole I'm working on (Thanks Hui for the advice).

http://i32.tinypic.com/2hqqm87.jpg

 

[Hui]

Post a picture to http://tinypic.com/

and then post a link to the image here

and have a read of

http://chandoo.org/forums/topic/macros-for-deleting-0

You should be able to use that code easily

 

[NeverHappyMike]

Hi


I've also posted this question on another forum I often use (www.superuser.com) and a user called "dkusleika" posted a wonderful reply that worked a charm. I tinkered a little to catch some other formats that I needed deleting and it worked perfectly.


Important note: He mentioned that when looping through a delete macro it's always best to start from the bottom and delete upwards. Thought you should know. And below is his code. Any comments about it should mention him.


Thanks

Michael


----dkusleika's code below-------

Sub DeleteRows()


Dim i As Long

Dim rng As Range


Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1))


For i = rng.Count To 1 Step -1

If rng.Cells(i).Value Like "..-*" Then

rng.Cells(i).EntireRow.Delete

ElseIf rng.Cells(i).Value Like ".-*" Then

rng.Cells(i).Value = Replace(rng.Cells(i).Value, ".-", "", 1, 1)

End If

Next i


End Sub

 

[Hui]

Michael

I am well aware of how to code those type of problems, but thankyou

If you are going to Multi post please let us know so we don't waste time reinventing the wheel

 

[NeverHappyMike]

@Hui: it was an urgent question so I used all my available resources to see if I could get it answered as quickly as possible. As for the reply and how it's posted, well I thought other non-Excel ninjas might find the code (and it's structure) useful. No offence was meant.