Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

My job is to:

  1. Delete the rows where Dot&Dot&Dash are used 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).

This file has over 600 lines

share|improve this question
There is meant to be an image with this post, but I can't see it. Hopefully you can. – RocketGoal Jul 12 '10 at 10:38
I can't see the image either. Do you have a link for it? – e.James Jul 12 '10 at 10:39
Fixed it. I removed the [img] tags. – RocketGoal Jul 12 '10 at 12:15
up vote 3 down vote accepted

When you delete rows in a loop, it's best to loop backwards. That way the internal loop counter doesn't get confused. Always, always, always make a backup of your data before you run code from the internet that deletes stuff.

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
        ElseIf rng.Cells(i).Value Like ".-*" Then
            rng.Cells(i).Value = Replace(rng.Cells(i).Value, ".-", "", 1, 1)
        End If
    Next i

End Sub
share|improve this answer
Good advice about not trusting code on the internet and an interesting way of working through the data, never thought to go backwards through it. Nice! – Mokubai Jul 12 '10 at 13:46
Thanks, I'll give it a go. I agree about backing up data, and have been doing whenever working with Internet code (and my own come to that matter). – RocketGoal Jul 12 '10 at 14:05
Like a charm. Many thanks. One to keep for my little VBA library. – RocketGoal Jul 12 '10 at 14:11

In place of a macro, you could:

  1. use the sort function
  2. kill your ..- rows
  3. use Find/Replace on ".-" (with nothing as the replacement option), and then
  4. re-sort your data.

This is a little more laborious than a macro, but it's an alternative anyway. dkusleika's solution looks like it should work.

share|improve this answer
Yeah, that's a good non-macro solution. If the original order is important, I'll put a new column with the numbers 1,2,3 for all the rows. Then I can resort on that column to get back to the original order. – dkusleika Jul 13 '10 at 14:14
For something simple like this I'd always go with find & replace as it is undo-able and in all likelihood quicker. – Lunatik Jul 14 '10 at 7:34

This is how I'd manage this:

For Each Cell In ActiveSheet.Range("A:A")
    If Left(Cell.Value, 3) = "..-" Then
    ElseIf Left(Cell.Value, 2) = ".-" Then
        Cell.Value = Right(Cell.Value, (Len(Cell.Value) - 2))
    End If

The only problem is that it fails to see the row after a deletion, so it may be better to do something like the following:

For Each Cell In ActiveSheet.Range("A:A")
    If Left(Cell.Value, 3) = "..-" Then
    ElseIf Cell.Value = "" Then
        Exit For
    End If
For Each Cell In ActiveSheet.Range("A:A")
    If Left(Cell.Value, 2) = ".-" Then
        Cell.Value = Right(Cell.Value, (Len(Cell.Value) - 2))
    ElseIf Left(Cell.Value, 1) = "-" Then
        Cell.Value = Right(Cell.Value, (Len(Cell.Value) - 1))
    ElseIf Cell.Value = "" Then
        Exit For
    End If

You end up iterating over the batch of cells twice, but it actually catches everything...

If you need to align a particular column, then try

With ActiveSheet.Columns("A")
    .HorizontalAlignment = xlLeft
End With
share|improve this answer
Many thanks, i'm off to give it a try. – RocketGoal Jul 12 '10 at 13:15
It seems quite temperamental. It doesn't delete the rows where there are two or more rows of "..-" together. Is there some way of asking it to go through the loop until there are no more ".. -" in the list before going on to the second part of the loop? – RocketGoal Jul 12 '10 at 13:42
As dkusleika said in his answer the cell counter gets a bit confused by the row deletion. I'll try and find out if I can modify it to make it a bit more friendly about the deletions, it may be that a 'While Left(Cell.Value, 3) = "..-" type arrangement may work' – Mokubai Jul 12 '10 at 13:48
@Mike Go with dkusleikas answer, seems that when deleting rows the "For Each" operator is not optimal. His solution gets around that kind of problem. – Mokubai Jul 12 '10 at 14:00
Thanks for you help Mokubai. – RocketGoal Jul 12 '10 at 14:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.