deleting a cell based on character length?

[Marcusdmc]

if I wanted to delete a cell in my selected row that was shorter than
say 16 characters, how would I create a macro to do that?

I would be checking for LEN(A:A) < 16 then delete?

Thanks for any help!

-Marcus

 

[Guest]

To clear the cell contents of anything with <16 characters in Column A, try
something like this:

Sub test()
Dim c As Range
For Each c In Application.Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("A:A"))
If Len(c.Value) < 16 Then
c.Clear
End If
Next c
End Sub

If you want to delete the entire row instead of just clearing the cell, then
something like this:

Sub test2()
Dim c As Range
Dim rngDelete As Range
For Each c In Application.Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("A:A"))
If Len(c.Value) < 16 Then
If rngDelete Is Nothing Then
Set rngDelete = c
Else
Set rngDelete = Application.Union(c, rngDelete)
End If
End If
Next c
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete

End Sub

 

[Marcusdmc]

To clear the cell contents of anything with <16 characters in Column A, try
something like this:

Sub test()
Dim c As Range
For Each c In Application.Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("A:A"))
If Len(c.Value) < 16 Then
c.Clear
End If
Next c
End Sub

If you want to delete the entire row instead of just clearing the cell, then
something like this:

Sub test2()
Dim c As Range
Dim rngDelete As Range
For Each c In Application.Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("A:A"))
If Len(c.Value) < 16 Then
If rngDelete Is Nothing Then
Set rngDelete = c
Else
Set rngDelete = Application.Union(c, rngDelete)
End If
End If
Next c
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete

End Sub

--
Hope that helps.

Vergel Adriano








- Show quoted text -

Thanks for the direction! Turned off screen updating to make it go
faster!