Clear rows where field contains a negative sign

  • Thread starter Thread starter ms
  • Start date Start date
M

ms

I have a procedure called that looks for a negative sign (*-*) in the D column
for all records. If it exists, the entire row is deleted. The procedure below
works well for this situation, BUT when there are no records with the negative
sign in the D column it deletes the first record in row 1. The Range somehow is
set for the value in the D column for row 1.
I have been unable to stop this so far.
Does anyone have a way to not delete row one if the filter is not met?

Here is the procedure:
Sub CleanCancelledChks()
Dim r As Range
With ActiveSheet
ActiveSheet.Columns("A:A").Select
Set r = .Range(.Range("D2"), .Range("D" & _
Rows.Count).End(xlUp))
.Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*"
Set r = r.SpecialCells(xlCellTypeVisible)
.AutoFilterMode = False
r.EntireRow.Delete
End With
End Sub

Here is 2 records as an example. The first one in the list will be deleted by
this procedure though the D column does not have a negative sign on the right
side of the value as shown in the next example below.
L 2222222221 063004 012121 500.00
L 1111111111 063004 021211 500.00

An example of the record with a negative on the D column:
L 2222222221 063004 012121- 500.00
 
Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If instr(Cells(row_index, 4).Value,"-")>0 then
Rows(row_index).delete
End If
Next
Application.ScreenUpdating = True
End Sub
 
Thank you Frank.
It correctly does not delete the first record if the D column does not have a
"-" But it also does not delete a record in the recordset if it Does have a
negative sign in the D column. I will try to figure out why, but if you have an
idea, I would be glad to hear it.
Thanks again for your help.
Mark Schatz
 
Hi,
I am searching for a negative sign at the end of any value in the D column.
Here is an example of a record where a negative sign exists in the D
column,(there are 5 columns).
A B C D E
L 2222222221 063004 012121- 500.00

Thank you,
mark
 
One way:

Public Sub Delete_neg_rows()
Dim rDelete As Range
Dim rCell As Range
Dim nLastRow As Long
nLastRow = Range("D" & Rows.Count).End(xlUp).Row
If nLastRow = 1 Then Exit Sub
For Each rCell In Range("D2:D" & nLastRow)
With rCell
If .Text Like "*-" Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub
 
It doesn't work.
Range("D" & Rows.Count).End(xlUp).Row is equal to 1 when I have 5 records and
one of them has a negative sign in column D. Is the purpose of this line to
count the number of records that exist? If so, why does it see only 1 record.
Here are the 5 records of the D column:
062417
062418
062419 -
062420
062421

The negative sign is on the 3 record. I need to delete the entire row/record
that contains that negative sign.

Thank you,
Mark
 
Are you running J.E.'s routine against the correct worksheet (make sure it's the
activesheet before you start the procedure).

Do you have a data|filter|autofilter turned on (and filtering so that only thea
header is showing)?

Do you have lots of hidden rows?

Are you sure that you have the data in column D?

Try to replicate this line of code manually:
Range("D" & Rows.Count).End(xlUp).Row

Put your cursor in D65536.
Hit the End key on the keyboard
Hit the up arrow

What's the activecell's address?
 
What does "doesn't work" mean? Are any rows deleted?

I can't reproduce your results - when I put your data in column D,
Range("D" & Rows.Count).End(xlUp).Row = 5, not 1 (as long as the
worksheet with the is active).

Do you have an autofilter applied? If so, is the row with the - sign
visible?
 
Dave and JE,
The anwsers to your questions are:
yes, no-I can see all records, no hidden rows, yes, I replicated it manually and
no difference, I cannot get to line D65536. It would take forever on holding
the down arrow on the scroll bar, I am not sure what the address of the active
cell is.

The recordset is from a text file that is first edited in another procedure
before this procedure is called so the file opened in excel is a space delimited
text file. I tested the procedure out with a new worksheet and added the code
and it works so it has to do with the records being still in text format though
they are in columns when viewed in excel while the procedures are running.
Do you have any idea why it cannot see the records in their proper columns as I
do while stepping thru the code?
 
One way to get to D65536:

Hit F5
type D65536
click ok

Maybe the data is all in column A. If you widen column A, it may be obvious.

If it's all in one column, select that column and do
data|text to columns
delimited
By a space
and finish up.

(Save your work first--just in case you screw up. You can close without
saving.)
 
Hi,
I did get to D65536 and then END then up arrow which took me to the last last
cell in the D column. I then ran the procedure and nLastRow still = 1. I
changed D2 to D1 in the line "For Each rCell In Range("D1:D" & nLastRow)". This
then change nLastRow = 4. I watch the rCell value and it stepped thru every
value in the D column EXCEPT the value with the negative sign.

The data is not all in column A. There is five separate columns. I understand
text to columns and that is already handled in a procedure run first.

thank you,
mark
 
Are you sure the correct sheet is active?

Or did you change the code that J.E. suggested? If you did, you may want to
post it.

(I don't have any other guesses.)
 
To the line:
I added Activesheet and now it sees the actual count of records correctly. BUT,
now the negative sign is not seen with .Text Like "*-". I assume it is because
the cell is not a Text cell but rather Custom specified in another procedure with:
sh.Columns("D:D").Select
Selection.NumberFormat = "0#####"
 
I added Activesheet the line: nLastRow = ActiveSheet.Range("D" &
Rows.Count).End(xlUp).Row and now it sees the actual count of records correctly!

BUT, now the negative sign is not seen with .Text Like "*-". I assume it is
because the cell is not a Text cell but rather Custom specified in another
procedure with:
sh.Columns("D:D").Select
Selection.NumberFormat = "0#####"
I tried to change the format to text then search for the negative sign but that
didn't work.
 
Do you have this procedure in a General module--not behind a worksheet and not
behind ThisWorkbook? (I'm betting not).

Try moving it there. (I'm betting that this will fix the problem.)

Numberformat won't alter any cell that is text--it only works on numbers.

And 123- (in your situation) is text.

(.Text refers to what you see in the cell--not the actual value. For instance,
if you put =1/3 in a cell, the value = 0.33333333333. But if you format it as
000.0%, the .text will return 033.3%.)
 
Back
Top