Help - Deleting Rows on Text Criteria
 

I've created some code that imports a fixed width text file and format
it into columns according to field length. Some of these rows begi
with "@1" and some begin with "@2". The client only wants the rows tha
begin with "@1". No problem (so I thought), I'll just create a loop t
go through the rows and delete rows that contain "@2" in column A
Except that I can't get it to work. Here is the code I'm using:

Sub DeleteRows()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A").Value = ["@2"] Then Cells(i
"A").EntireRow.Delete
Next i
End Sub

Nothing happens. The code executes without errors but no rows ar
deleted.

I thought maybe the worksheet wasn't active but when I add a line o
code to select a cell on the worksheet it is selected after th
procedure is finished. I also thought that maybe it was the format o
the cells so I tried formatting the column to text before the loop bu
that didn't work either. I need another set of eyes to point ou
something I think may be obvious that I'm missing. Any thoughts

--
rayd
-----------------------------------------------------------------------
rayd8's Profile: http://www.excelforum.com/member.php...fo&userid=2626
View this thread: http://www.excelforum.com/showthread.php?threadid=39709

Help - Deleting Rows on Text Criteria
 

You said "begin with"
A little modification, I am using LEFT


Private Sub CommandButton1_Click()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Left(Cells(i, "A").Value, 2) = ["@2"] Then Cells(i, "A").EntireRow.Delete
Next i
End Sub


If this does not work, send a snapshot of your data.

Mangesh





"rayd8" wrote in
message ...

I've created some code that imports a fixed width text file and formats
it into columns according to field length. Some of these rows begin
with "@1" and some begin with "@2". The client only wants the rows that
begin with "@1". No problem (so I thought), I'll just create a loop to
go through the rows and delete rows that contain "@2" in column A.
Except that I can't get it to work. Here is the code I'm using:

Sub DeleteRows()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A").Value = ["@2"] Then Cells(i,
"A").EntireRow.Delete
Next i
End Sub

Nothing happens. The code executes without errors but no rows are
deleted.

I thought maybe the worksheet wasn't active but when I add a line of
code to select a cell on the worksheet it is selected after the
procedure is finished. I also thought that maybe it was the format of
the cells so I tried formatting the column to text before the loop but
that didn't work either. I need another set of eyes to point out
something I think may be obvious that I'm missing. Any thoughts?


--
rayd8
------------------------------------------------------------------------
rayd8's Profile:
http://www.excelforum.com/member.php...o&userid=26267
View this thread: http://www.excelforum.com/showthread...hreadid=397094

Help - Deleting Rows on Text Criteria
 

Hers another variation

Sub DeleteRows()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Left(Cells(i, "A").Value, 2) = "@2" Then Rows(i).Delete
Next i
End Su

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=39709

Help - Deleting Rows on Text Criteria
 

On Thu, 18 Aug 2005 22:09:29 -0500, rayd8
wrote:


I've created some code that imports a fixed width text file and formats
it into columns according to field length. Some of these rows begin
with "@1" and some begin with "@2". The client only wants the rows that
begin with "@1". No problem (so I thought), I'll just create a loop to
go through the rows and delete rows that contain "@2" in column A.
Except that I can't get it to work. Here is the code I'm using:

Just a thought, but wouldn't it be quicker to filter the whole sheet
to display the "@2" rows, (or preferably rows Not Equal to @1) and
then use the

Application.DisplayAlerts = False
Range("your_range").SpecialCells(xlCellTypeVisible ).Rows.Delete

then switch the filter off?

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Help - Deleting Rows on Text Criteria
 

Thanks for all of your help but it still doesn't work (on the worksheet
in question). The code that everyone suggested works because I tested
it with the same data on a stand-alone worksheet. (Because I'm creating
this solution to import and format text files it creates another
workbook, loads the data and then I perform the formatting
procedures).

This leads me to believe that I am not properly selecting the
(workbook)worksheet before calling this procedure. This confuses me
because I even inserted the code in a procedure that does some
formatting on the same worksheet and that works fine. It's like the
code is completely ignored. Sorry, I cannot send sample data as I am
doing this in my spare time for another department at the university I
work for and it is confidential student information protected by
Federal privacy laws.

I'm going to have to do some more research as to why this is happening.
If any of you have any suggestions I'd appreciate it. Again, thanks to
everyone for your prompt response to my original question.


--
rayd8
------------------------------------------------------------------------
rayd8's Profile: http://www.excelforum.com/member.php...o&userid=26267
View this thread: http://www.excelforum.com/showthread...hreadid=397094

Help - Deleting Rows on Text Criteria
 

Another possiblity could be that '@2' is not exactly where it is supposed to
be. Try to use the FIND function to see the position of @2 in your string.
is it 1, then the code should work. Another thing, in the code itself you
can specify which sheet to look for, and thus ensure that it is looking at
the correct place. Check the modification in the code below. I have added
Worksheets("Sheet1"). for each Cells. Change the sheet name to suit your
need.

Private Sub CommandButton1_Click()
Dim i As Long
For i = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row To 2
Step -1
If Left(Worksheets("Sheet1").Cells(i, "A").Value, 2) = ["@2"] Then
Worksheets("Sheet1").Cells(i, "A").EntireRow.Delete
Next i
End Sub


Mangesh



"rayd8" wrote in message
...

Thanks for all of your help but it still doesn't work (on the worksheet
in question). The code that everyone suggested works because I tested
it with the same data on a stand-alone worksheet. (Because I'm creating
this solution to import and format text files it creates another
workbook, loads the data and then I perform the formatting
procedures).

This leads me to believe that I am not properly selecting the
(workbook)worksheet before calling this procedure. This confuses me
because I even inserted the code in a procedure that does some
formatting on the same worksheet and that works fine. It's like the
code is completely ignored. Sorry, I cannot send sample data as I am
doing this in my spare time for another department at the university I
work for and it is confidential student information protected by
Federal privacy laws.

I'm going to have to do some more research as to why this is happening.
If any of you have any suggestions I'd appreciate it. Again, thanks to
everyone for your prompt response to my original question.


--
rayd8
------------------------------------------------------------------------
rayd8's Profile:
http://www.excelforum.com/member.php...o&userid=26267
View this thread: http://www.excelforum.com/showthread...hreadid=397094

Help - Deleting Rows on Text Criteria
 

Mangesh,

Thanks so much for your help. I was able to get it working with your
code. This was a major headache for me trying to get this figured out
and I really appreciate your assistance.


--
rayd8
------------------------------------------------------------------------
rayd8's Profile: http://www.excelforum.com/member.php...o&userid=26267
View this thread: http://www.excelforum.com/showthread...hreadid=397094

Help - Deleting Rows on Text Criteria
 

Glad it works. Thanks for the feedback.

Mangesh


"rayd8" wrote in
message ...

Mangesh,

Thanks so much for your help. I was able to get it working with your
code. This was a major headache for me trying to get this figured out
and I really appreciate your assistance.


--
rayd8
------------------------------------------------------------------------
rayd8's Profile:
http://www.excelforum.com/member.php...o&userid=26267
View this thread: http://www.excelforum.com/showthread...hreadid=397094