[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Re: Never-ending For Next Loop

Don Guillett

12/19/2006 2:53:00 PM

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
dguillett1@austin.rr.com
"Helen" <Helen@discussions.microsoft.com> wrote in message
news:4B766DC9-AA29-4CCF-80DC-2208C5229CE4@microsoft.com...
>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?


3 Answers

Helen

12/19/2006 3:36:00 PM

0

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
> dguillett1@austin.rr.com
> "Helen" <Helen@discussions.microsoft.com> wrote in message
> news:4B766DC9-AA29-4CCF-80DC-2208C5229CE4@microsoft.com...
> >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?
>
>
>

John Bundy

12/19/2006 3:45:00 PM

0

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
> > dguillett1@austin.rr.com
> > "Helen" <Helen@discussions.microsoft.com> wrote in message
> > news:4B766DC9-AA29-4CCF-80DC-2208C5229CE4@microsoft.com...
> > >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?
> >
> >
> >

Don Guillett

12/19/2006 4:08:00 PM

0

Actually,
to 4 in this case

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"John Bundy" <JohnBundy@discussions.microsoft.com> wrote in message
news:87E0E8E8-163F-480B-BB4C-34E773F0E882@microsoft.com...
> 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
>> > dguillett1@austin.rr.com
>> > "Helen" <Helen@discussions.microsoft.com> wrote in message
>> > news:4B766DC9-AA29-4CCF-80DC-2208C5229CE4@microsoft.com...
>> > >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?
>> >
>> >
>> >