VBA: How can I delete specific ROWS and then remove remaining dots and dashes? « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

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

(5 posts) (2 voices)

Started 2 years ago by NeverHappyMike
Latest reply from NeverHappyMike

Tags:

NeverHappyMike

Member
Joined: Jun '10
Posts: 29

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

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,041

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

Posted 2 years ago #
NeverHappyMike

Member
Joined: Jun '10
Posts: 29

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

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,041

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

Posted 2 years ago #
NeverHappyMike

Member
Joined: Jun '10
Posts: 29

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

Posted 2 years ago #