deleting a cell based on character length?
 

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

deleting a cell based on character length?
 

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


"Marcusdmc" wrote:

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

deleting a cell based on character length?
 

On Aug 7, 12:30 pm, Vergel Adriano
wrote:
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



"Marcusdmc" wrote:
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- Hide quoted text -

- Show quoted text -

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