Macro - Delete Rows of a Certain Letter Case

**godonnygo** · 12-29-2007, 07:27 AM

Hi, I have some data in Excel that follows a pattern similar to the following:

   |                A                 |             B           |          C      |
1 | #1 - AAAAAAA - AAAA
2 | Abcdef
3 |
4 | #2 - BBBBBBB - BBBB
5 | Bcdefg
6 |
7 | #3 - CCCCCC - CCCC
8 | Cdefgh

What I want in this example is for the rows that are in "proper" case (or whatever you call a capital followed by lowecase letters), ie A2, A5, A8, to be deleted by a macro.

Does anyone think this could be done? I'm not really sure if Excel/VBA can differentiate between cases.

**StephenR** · 12-29-2007, 08:40 AM

Try this.

Sub x()

Dim RegEx As Object, RegMatchCollection As Object, RegMatch As Object
Dim rng As Range, OutPutStr As String

Set RegEx = CreateObject("vbscript.regexp")

With RegEx
    .Global = True
    .Pattern = "^[A-Z][a-z]+"
End With

For Each rng In ActiveSheet.Range("A1:A6") ' amend to suit
    OutPutStr = ""
    Set RegMatchCollection = RegEx.Execute(rng.Value)
    For Each RegMatch In RegMatchCollection
        OutPutStr = OutPutStr & RegMatch
    Next
    If OutPutStr <> "" Then rng.EntireRow.Delete
Next

End Sub

**mudraker** · 12-29-2007, 08:42 AM

Here is another macro that you could also try

Sub DeleteCells()
   Dim lRow As Long
   
   For lRow = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
      With Cells(lRow, "a")
         If .Value = Application.Proper(.Value) Then
            If .Value <> "" Then
               .Delete Shift:=xlUp
            End If
         End If
      End With
   Next lRow
End Sub

**royUK** · 12-29-2007, 10:48 AM

And another way, I prefer to find all the cells then do one delete

Option Explicit

Sub deleteRows()
    Dim cl     As Range
    Dim rng    As Range
    Dim R      As Long
    Dim X      As Long

    R = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For X = 1 To R
        If Not IsEmpty(Cells(X, 1)) And Cells(X, 1).Value = Application.Proper(Cells(X, 1).Value) Then
            If rng Is Nothing Then
                Set rng = Cells(X, 1)
            Else: Set rng = Union(rng, Cells(X, 1))
            End If
        End If
    Next X
    rng.EntireRow.Delete
End Sub

**godonnygo** · 12-30-2007, 01:03 AM

Yeah Roy, I was thinking the find and delete option could be worth trying.

Thanks very much guys, I'll give them all a go when I get a chance. Great work.