Delete row if column A cell text length is not 11

[John Keith]

I want to loop through the data in column A and if the text length of
the contents of the cell is not equal to 11 I want to delete the row.

Here's what I have that is not working (error is function not
compatible with object or something to that effect):

For i = Range("A:A").Rows.Count To 1 Step -1
If WorksheetFunction.Len(Range("A:A").Rows(i)) <> 11 Then
Range("A:A").Rows(i).EntireRow.Delete
End If
Next i

Can someone point out what I'm doing wrong, or suggest a better
method?

Thanks




John Keith
(e-mail address removed)

 

[imageswords.br]

Hi John,
this should do the trick.
You dont need to invoke the worksheet function to return the length.
Also you where testing the length of the Row
"Len(Range("A:A").Rows(i)) <> 11" when you should be testing the
length of the value of the cell.
I think this is where you were going wrong.
Let me know if you have a problem

Kind regards
Bernie

-----------------------------------------------------------------------


Public Sub DeleteTheRows()
Dim i As Double 'Cant use and integer because it cant hold the value
65536.

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For i = Worksheets(1).Range("A:A").Rows.Count To 1 Step -1
If Len(Range("A:A").Cells(i)) <> 11 Then
Range("A:A").Cells(i).EntireRow.Delete
End If
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

 

[John Keith]

Hi John,
this should do the trick.
You dont need to invoke the worksheet function to return the length.
Also you where testing the length of the Row
"Len(Range("A:A").Rows(i)) <> 11" when you should be testing the
length of the value of the cell.
I think this is where you were going wrong.
Let me know if you have a problem

Bernie,

Perfect! Thank you very much.

Let me ask a couple of questions (I'm still learning).

When I use the ".Calculation" I get a compile error but all is OK when
I remove the ".", what's going on with that?

I have understood that not all worksheet functions are available in a
macro using the same name so that I thought that is why the
"WorksheetFunction" was used. Obviously that is not the case with the
LEN function. How do I know when I have to use the worksheet function?

Finally, what is your suggestion on the best way to become more
proficient with excel macros beyond the hands on experiments I'm doing
now?

Thanks again!


John Keith
(e-mail address removed)

 

[Dave Peterson]

Dim LastRow as long
dim i As long
With activesheet
'just look at the rows that are used
lastrow = .cells(.rows.count,"A").end(xlup).row

For i = lastrow to 1 Step -1
If len(.cells(i,"A").value) <> 11 then
.Rows(i).Delete
End If
Next i
End with

 

[John Keith]

Dim LastRow as long
dim i As long
With activesheet
'just look at the rows that are used
lastrow = .cells(.rows.count,"A").end(xlup).row

For i = lastrow to 1 Step -1
If len(.cells(i,"A").value) <> 11 then
.Rows(i).Delete
End If
Next i
End with

Dave,

Thank you for your solution. It has given me a key for another problem
I need to solve.


John Keith
(e-mail address removed)