Deleting rows that meet criteria
 

Hi, I know little about VBA but know a macro can really speed up a
tedious weekly task, if only I can figure out how to write it.
What I need: I have a report imported into Excel. This report
contains several headers that are repeated throughout and I want to
delete those rows. From browsing the newsgroup I found this sample
code:

Public Sub SelectiveDelete()
Dim LRowData As Long, ir As Long


With Sheets("Sheet1")
' detect last row of data in column A
LRowData = .Cells(Rows.Count, "A").End(xlUp).Row


' scan list testing if the value is in range
For ir = LRowData To 1 Step -1
If Trim(.Cells(ir, 1).Value) = "A/C No" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" Or _
InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _
Len(Trim(.Cells(ir, 2).Value)) = 0 _
Then .Rows(ir).Delete Shift:=xlUp
Next ir
End With

End Sub

This works except that I will need to add more Value Lines with my
criteria; I know how to do that.
However, I do not need to look for anything in Column B yet if I get
rid of those lines, it comes up with a syntax error. What do I need
for the last three lines if all I want is to delete rows that have
specific values in Column A as in the two lines with Trim....

Also, it appears that one of the rows that is imported that I want to
delete has a character that looks like a check box in it (and that is
all) Anyone know how to type this in Excel?

Thanks for any help, Bevin B.

Deleting rows that meet criteria
 

Hi Bevin,

Did you delete the Or preceding the section you deleted? (After "Report
Total")

If Trim(.Cells(ir, 1).Value) = "A/C No" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" _
Then .Rows(ir).Delete Shift:=xlUp

If this is not your problem, post the code as you have amended it.


Regards,

OssieMac

" wrote:

Hi, I know little about VBA but know a macro can really speed up a
tedious weekly task, if only I can figure out how to write it.
What I need: I have a report imported into Excel. This report
contains several headers that are repeated throughout and I want to
delete those rows. From browsing the newsgroup I found this sample
code:

Public Sub SelectiveDelete()
Dim LRowData As Long, ir As Long


With Sheets("Sheet1")
' detect last row of data in column A
LRowData = .Cells(Rows.Count, "A").End(xlUp).Row


' scan list testing if the value is in range
For ir = LRowData To 1 Step -1
If Trim(.Cells(ir, 1).Value) = "A/C No" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" Or _
InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _
Len(Trim(.Cells(ir, 2).Value)) = 0 _
Then .Rows(ir).Delete Shift:=xlUp
Next ir
End With

End Sub

This works except that I will need to add more Value Lines with my
criteria; I know how to do that.
However, I do not need to look for anything in Column B yet if I get
rid of those lines, it comes up with a syntax error. What do I need
for the last three lines if all I want is to delete rows that have
specific values in Column A as in the two lines with Trim....

Also, it appears that one of the rows that is imported that I want to
delete has a character that looks like a check box in it (and that is
all) Anyone know how to type this in Excel?

Thanks for any help, Bevin B.

Deleting rows that meet criteria
 

Hi again Bevin,

Forgot to answer your second question. With unknown characters, you can find
the ASCII code for it and use chr() to represent it.

If it is the only character in the cell then in another cell insert =CODE(A1)
where A1 is the required cell.
The number returned is the code for the character. If it is 10 then chr(10)
represents it and you do not enclose it in double quotes.

If there are multiple characters in the cell then use something like this:-
=CODE(MID(F2,5,1))
where F2 is the cell containing the data.
5 its the number of the character counting from the left.
1 says only return for one character.

Regards,

OssieMac


Regards,

OssieMac

" wrote:

Hi, I know little about VBA but know a macro can really speed up a
tedious weekly task, if only I can figure out how to write it.
What I need: I have a report imported into Excel. This report
contains several headers that are repeated throughout and I want to
delete those rows. From browsing the newsgroup I found this sample
code:

Public Sub SelectiveDelete()
Dim LRowData As Long, ir As Long


With Sheets("Sheet1")
' detect last row of data in column A
LRowData = .Cells(Rows.Count, "A").End(xlUp).Row


' scan list testing if the value is in range
For ir = LRowData To 1 Step -1
If Trim(.Cells(ir, 1).Value) = "A/C No" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" Or _
InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _
Len(Trim(.Cells(ir, 2).Value)) = 0 _
Then .Rows(ir).Delete Shift:=xlUp
Next ir
End With

End Sub

This works except that I will need to add more Value Lines with my
criteria; I know how to do that.
However, I do not need to look for anything in Column B yet if I get
rid of those lines, it comes up with a syntax error. What do I need
for the last three lines if all I want is to delete rows that have
specific values in Column A as in the two lines with Trim....

Also, it appears that one of the rows that is imported that I want to
delete has a character that looks like a check box in it (and that is
all) Anyone know how to type this in Excel?

Thanks for any help, Bevin B.

Deleting rows that meet criteria
 

On Nov 13, 9:35 pm, OssieMac
wrote:
Hi Bevin,

Did you delete the Or preceding the section you deleted? (After "Report
Total")

If Trim(.Cells(ir, 1).Value) = "A/C No" Or _
Trim(.Cells(ir, 1).Value) = "Report Total" _
Then .Rows(ir).Delete Shift:=xlUp

If this is not your problem, post the code as you have amended it.

Regards,

OssieMac
No, I didn't, that will probably work...
Thanks, Bevin