something like this to delete the rows

for i=cells(rows.count,"a").end(xlup).row to 2 step -1
if len(cells(i,"a"))<3 then rows(i).delete
next i

--
Don Guillett
SalesAid Software

"Helen" wrote in message
...
I want to delete any lines in a spreadsheet where the first column doesn't
conform to a three character code. This includes blank cells, but also
strings with more or less characters. The code should start from Row 4.

I came up with the following:

Sheets("Purchase Report").Select
Range("A1").Select

myRows = Selection.CurrentRegion.Rows.Count

Cells(4, 1).Select

For x = 4 To myRows

Cells(x, 1).Select

Company = Sheets("Purchase Report").Cells(x, 1)

If Len(Company) = 3 Then

GoTo PlantNumberOK

Else

Rows(x).Delete

x = x - 1

myRows = myRows - 1

PlantNumberOK:

End If

Next x

Somehow when I run this X can become a higher number than myRows, and when
it runs out of data it starts to delete the blank cells beneath the myRows
set. What have I done wrong?

Actually,
to 4 in this case

--
Don Guillett
SalesAid Software

"John Bundy" wrote in message
...
The 2 in the for statement tells where to stop, change to a 3 as in below

for i=cells(rows.count,"a").end(xlup).row to 3 step -1

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Helen" wrote:

The first three lines of the table are headings, is there a way to
customise
this code so that it ignores the first 3 rows?

"Don Guillett" wrote:

something like this to delete the rows

for i=cells(rows.count,"a").end(xlup).row to 2 step -1
if len(cells(i,"a"))<3 then rows(i).delete
next i

--
Don Guillett
SalesAid Software

"Helen" wrote in message
...
I want to delete any lines in a spreadsheet where the first column
doesn't
conform to a three character code. This includes blank cells, but
also
strings with more or less characters. The code should start from Row
4.

I came up with the following:

Sheets("Purchase Report").Select
Range("A1").Select

myRows = Selection.CurrentRegion.Rows.Count

Cells(4, 1).Select

For x = 4 To myRows

Cells(x, 1).Select

Company = Sheets("Purchase Report").Cells(x, 1)

If Len(Company) = 3 Then

GoTo PlantNumberOK

Else

Rows(x).Delete

x = x - 1

myRows = myRows - 1

PlantNumberOK:

End If

Next x

Somehow when I run this X can become a higher number than myRows, and
when
it runs out of data it starts to delete the blank cells beneath the
myRows
set. What have I done wrong?