Excel VBA/Macro: how can I delete rows and remove dots & dashes?

Question

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

Answer 1

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

Answer 2

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.

Answer 3

This is how I'd manage this:

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

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
        Cell.EntireRow.Delete
    ElseIf Cell.Value = "" Then
        Exit For
    End If
Next
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
Next

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