Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
count rows that meet certain criteria | Excel Discussion (Misc queries) | |||
Need help please for deleting rows that does not meet several criteria | Excel Programming | |||
Deleting/IDing Rows that Don't Meet Criteria | Excel Worksheet Functions | |||
Deleting/IDing Rows that Don't Meet Criteria | Excel Worksheet Functions |