Delete all cell data that is not all CAPS

[overbet]

Does anyone know how to delete all cell data that is not all CAPS and leave everything that is all CAPS? Ive tried these two pieces of code I found online, but neither works. The first clears everything and the second deletes the blank rows only. Thanks

Sub Delete_Cells_Without_Caps()


Dim k As Long


For k = 1 To 50000


     '   If Worksheets("junk").Cells(k, "b").Value <> "Caps" Then
        Worksheets("junk").Cells(k, "a").ClearContents
      '  End If
Next
End Sub

Sub Delete_Cells_Without_Caps()
For i = 50000 To 1 Step -1
If Range("A" & i).Value = LCase(Range("A" & i).Value) Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub

 

[Fluff]

Try changing

If Range("A" & i).Value = LCase(Range("A" & i).Value) Then

to

If Range("A" & i).Value <> UCase(Range("A" & i).Value) Then

 

[JoeMo]

Select the cells you want to apply this to first, then run this macro.

Sub ClearIfNotAllCaps()
'select cells first, then run this macro
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection
    If UCase(c.Value) <> c.Value Then c.ClearContents
Next c
Application.ScreenUpdating = True
End Sub

 

[overbet]

Thank you both. Joe, your code works amazing. Fast and perfect. Thank you very much for your help and time. Cheers

 

[JoeMo]

Thank you both. Joe, your code works amazing. Fast and perfect. Thank you very much for your help and time. Cheers

You are welcome - thanks for the reply.

 

[Rick Rothstein]

Does anyone know how to delete all cell data that is not all CAPS and leave everything that is all CAPS? Ive tried these two pieces of code I found online, but neither works. The first clears everything and the second deletes the blank rows only.

Your question is open to some interpretation. For example, you code is testing against the text "Caps" which leads one to wonder if you meant the text "CAPS" or is CAPS was meant as all uppercase characters. If you did mean all uppercase characters, then letters are obvious, but what about numbers and punctuation marks... their lowercase and uppercase representations are identical, so some would say they are all caps as well (for example, AB.CD or XYZ123... would they be considered all caps?). Please clarify.

 

[overbet]

Hi Rick, sorry about that. I did me all CAPS (capital/upper case letters). Thank you for pointing that out. I will try to be more clear next time. Regards

 

[Rick Rothstein]

Select the cells you want to apply this to first, then run this macro.

Sub ClearIfNotAllCaps()
'select cells first, then run this macro
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection
    If UCase(c.Value) <> c.Value Then c.ClearContents
Next c
Application.ScreenUpdating = True
End Sub

If I am not mistaken, I believe this one-liner will do the same thing as your code does...

[table="width: 500"]
[tr]
	[td]Sub ClearIfNotAllCaps()
  Selection = Evaluate(Replace("IF(EXACT(@,UPPER(@)),@,"""")", "@", Selection.Address))
End Sub[/td]
[/tr]
[/table]

 

[JoeMo]

If I am not mistaken, I believe this one-liner will do the same thing as your code does...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ClearIfNotAllCaps()
  Selection = Evaluate(Replace("IF(EXACT(@,UPPER(@)),@,"""")", "@", Selection.Address))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Nice!