VBA: How to delete rows based on the small case letter in a particular column.

[Rabiyudeen]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]S.NO.[/TD]
[TD]NAME[/TD]
[TD]AGE[/TD]
[TD]LOCATION[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]JOHN abraham[/TD]
[TD]34[/TD]
[TD]WASHINGTON[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 165"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ANTONY RAJ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]30[/TD]
[TD]NEW YORK[/TD]
[/TR]
</tbody>[/TABLE]







Hi everyone,

I need to delete the row in column B if the name has small case letter. For example in JOHN abraham , abraham is in small case so that particular row should be deleted.

I just wanted to know do we able to delete the row based on the small case letter in that particular column. If yes, please let me know the VBA code. Thanks

 

[GTO]

To b explicit, are you saying that if an entire word (name, first or last) is in lower case, delete the row; or, are we looking to find one or more letters in lower case?

 

[Rabiyudeen]

Thanks for your response. We are looking to find any one or more letters in lower case (name, first or last ) then the entire row has to be deleted.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this in a copy of your workbook.

Sub Del_If_Any_Lower()
  Dim a, b
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If UCase(a(i, 1)) <> a(i, 1) Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[Rabiyudeen]

Thanks a lot Peter. Its working fine. Cheers

 

[Peter_SSs]

Thanks a lot Peter. Its working fine. Cheers

Glad it helped.