Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if column A cell text length is not 11
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if column A cell text length is not 11
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if column A cell text length is not 11
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if column A cell text length is not 11
On Tue, 30 Sep 2008 07:53:26 -0500, Dave Peterson
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to column fixed length | Excel Discussion (Misc queries) | |||
Summing a column based on the length of text in a cell in anothercolumn | Excel Worksheet Functions | |||
Code to set text length in cell | Excel Programming | |||
Search a Column by text length | Excel Worksheet Functions | |||
The text length versus the cell size... | Excel Programming |