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

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
>
>

On Aug 7, 12:30 pm, Vergel Adriano
<VergelAdri...@discussions.microsoft.com> 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!