I need a macro to search through a sheet of approximately 5,000 rows and
delete any row where the length of the string in column A is greater than 5.
I'm using Excel 2003. Can anyone help?

This should work:


Sub delSixUp()
Dim lastRow As Long, i As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = lastRow To 2 Step -1
If Len(Trim(ActiveSheet.Cells(i, 1).Value)) 5 Then
Cells(i, 1).EntireRow.Delete
End If
Next
End Sub




"Ken G" wrote in message
...
I need a macro to search through a sheet of approximately 5,000 rows and
delete any row where the length of the string in column A is greater than
5.
I'm using Excel 2003. Can anyone help?

Try

Sub MyMacro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Len(Range("A" & lngRow)) 5 Then Rows(lngRow).Delete
End If
Next
End Sub

--
Jacob


"Ken G" wrote:

I need a macro to search through a sheet of approximately 5,000 rows and
delete any row where the length of the string in column A is greater than 5.
I'm using Excel 2003. Can anyone help?

Thanks Jacob. Perfect.

"Jacob Skaria" wrote:

Try

Sub MyMacro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Len(Range("A" & lngRow)) 5 Then Rows(lngRow).Delete
End If
Next
End Sub

--
Jacob


"Ken G" wrote:

I need a macro to search through a sheet of approximately 5,000 rows and
delete any row where the length of the string in column A is greater than 5.
I'm using Excel 2003. Can anyone help?

Thanks also. This works just as well as the previous suggestion.

Problem solved!

"JLGWhiz" wrote:

This should work:


Sub delSixUp()
Dim lastRow As Long, i As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = lastRow To 2 Step -1
If Len(Trim(ActiveSheet.Cells(i, 1).Value)) 5 Then
Cells(i, 1).EntireRow.Delete
End If
Next
End Sub




"Ken G" wrote in message
...
I need a macro to search through a sheet of approximately 5,000 rows and
delete any row where the length of the string in column A is greater than
5.
I'm using Excel 2003. Can anyone help?


.