Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in the
I need to create what I thought would be a very simple macro; select some
rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in the
in an adjacent column
=if(left(E2,3)="www",if(right(E2,1)*11,na(),"")," ") then drag fill down the column select the column with the formula and do Edit=Goto=Special, select Formulas and uncheck everything except errors. Click OK Do Edit=Delete and select entire row delete this column. It takes longer to describe than it does to do. -- Regards, Tom Ogilvy "Dwoeltje" wrote: I need to create what I thought would be a very simple macro; select some rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in
I appreciate the response but I don't think you understood what I described
(or maybe I didn't describe it well enough), so I don't think this is going to help me. But I appreciate the help. "Tom Ogilvy" wrote: in an adjacent column =if(left(E2,3)="www",if(right(E2,1)*11,na(),"")," ") then drag fill down the column select the column with the formula and do Edit=Goto=Special, select Formulas and uncheck everything except errors. Click OK Do Edit=Delete and select entire row delete this column. It takes longer to describe than it does to do. -- Regards, Tom Ogilvy "Dwoeltje" wrote: I need to create what I thought would be a very simple macro; select some rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in
There must be some way, using VBA source code that I can paste into a macro,
that can search for and select all rows that contain a cell that has a value of www2 or www3 or www5 and then delete all those rows (leaving only the rows that have a cell that contains the value www1). Then I can save that macro in the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel. I tried using the macro recorder but with no way to select the rows based on whether a cell was equal to a certain value, all I could do was turn the "relative" feature on and then manually select all the rows that had cells containing www2 or www3 or www5. The problem with that is if I add more rows, that relative setting will be wrong and the macro will need to be redone, so using the macro recorder that way just doesn't help. Almost makes me wish I was using Access instead of Excel. "Tom Ogilvy" wrote: in an adjacent column =if(left(E2,3)="www",if(right(E2,1)*11,na(),"")," ") then drag fill down the column select the column with the formula and do Edit=Goto=Special, select Formulas and uncheck everything except errors. Click OK Do Edit=Delete and select entire row delete this column. It takes longer to describe than it does to do. -- Regards, Tom Ogilvy "Dwoeltje" wrote: I need to create what I thought would be a very simple macro; select some rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in
Couldn't you just use simple code like
For k=cells(rows.count,"e").end(xlup).row to 2 step-1 if cells(k,"e")<"www1" then rows(k).entirerow.delete next k "Dwoeltje" wrote in message ... There must be some way, using VBA source code that I can paste into a macro, that can search for and select all rows that contain a cell that has a value of www2 or www3 or www5 and then delete all those rows (leaving only the rows that have a cell that contains the value www1). Then I can save that macro in the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel. I tried using the macro recorder but with no way to select the rows based on whether a cell was equal to a certain value, all I could do was turn the "relative" feature on and then manually select all the rows that had cells containing www2 or www3 or www5. The problem with that is if I add more rows, that relative setting will be wrong and the macro will need to be redone, so using the macro recorder that way just doesn't help. Almost makes me wish I was using Access instead of Excel. "Tom Ogilvy" wrote: in an adjacent column =if(left(E2,3)="www",if(right(E2,1)*11,na(),"")," ") then drag fill down the column select the column with the formula and do Edit=Goto=Special, select Formulas and uncheck everything except errors. Click OK Do Edit=Delete and select entire row delete this column. It takes longer to describe than it does to do. -- Regards, Tom Ogilvy "Dwoeltje" wrote: I need to create what I thought would be a very simple macro; select some rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in
I don't know. Maybe. I'll give it a try.
"Zone" wrote: Couldn't you just use simple code like For k=cells(rows.count,"e").end(xlup).row to 2 step-1 if cells(k,"e")<"www1" then rows(k).entirerow.delete next k "Dwoeltje" wrote in message ... There must be some way, using VBA source code that I can paste into a macro, that can search for and select all rows that contain a cell that has a value of www2 or www3 or www5 and then delete all those rows (leaving only the rows that have a cell that contains the value www1). Then I can save that macro in the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel. I tried using the macro recorder but with no way to select the rows based on whether a cell was equal to a certain value, all I could do was turn the "relative" feature on and then manually select all the rows that had cells containing www2 or www3 or www5. The problem with that is if I add more rows, that relative setting will be wrong and the macro will need to be redone, so using the macro recorder that way just doesn't help. Almost makes me wish I was using Access instead of Excel. "Tom Ogilvy" wrote: in an adjacent column =if(left(E2,3)="www",if(right(E2,1)*11,na(),"")," ") then drag fill down the column select the column with the formula and do Edit=Goto=Special, select Formulas and uncheck everything except errors. Click OK Do Edit=Delete and select entire row delete this column. It takes longer to describe than it does to do. -- Regards, Tom Ogilvy "Dwoeltje" wrote: I need to create what I thought would be a very simple macro; select some rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in
That worked. Thanks.
"Zone" wrote: Couldn't you just use simple code like For k=cells(rows.count,"e").end(xlup).row to 2 step-1 if cells(k,"e")<"www1" then rows(k).entirerow.delete next k "Dwoeltje" wrote in message ... There must be some way, using VBA source code that I can paste into a macro, that can search for and select all rows that contain a cell that has a value of www2 or www3 or www5 and then delete all those rows (leaving only the rows that have a cell that contains the value www1). Then I can save that macro in the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel. I tried using the macro recorder but with no way to select the rows based on whether a cell was equal to a certain value, all I could do was turn the "relative" feature on and then manually select all the rows that had cells containing www2 or www3 or www5. The problem with that is if I add more rows, that relative setting will be wrong and the macro will need to be redone, so using the macro recorder that way just doesn't help. Almost makes me wish I was using Access instead of Excel. "Tom Ogilvy" wrote: in an adjacent column =if(left(E2,3)="www",if(right(E2,1)*11,na(),"")," ") then drag fill down the column select the column with the formula and do Edit=Goto=Special, select Formulas and uncheck everything except errors. Click OK Do Edit=Delete and select entire row delete this column. It takes longer to describe than it does to do. -- Regards, Tom Ogilvy "Dwoeltje" wrote: I need to create what I thought would be a very simple macro; select some rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select & deleting all rows based on the contents of a cell in
You're welcome! Thanks for the feedback.
"Dwoeltje" wrote in message ... That worked. Thanks. "Zone" wrote: Couldn't you just use simple code like For k=cells(rows.count,"e").end(xlup).row to 2 step-1 if cells(k,"e")<"www1" then rows(k).entirerow.delete next k "Dwoeltje" wrote in message ... There must be some way, using VBA source code that I can paste into a macro, that can search for and select all rows that contain a cell that has a value of www2 or www3 or www5 and then delete all those rows (leaving only the rows that have a cell that contains the value www1). Then I can save that macro in the Personal workbook for use as CTRL+SHIFT+E whenever I'm in Excel. I tried using the macro recorder but with no way to select the rows based on whether a cell was equal to a certain value, all I could do was turn the "relative" feature on and then manually select all the rows that had cells containing www2 or www3 or www5. The problem with that is if I add more rows, that relative setting will be wrong and the macro will need to be redone, so using the macro recorder that way just doesn't help. Almost makes me wish I was using Access instead of Excel. "Tom Ogilvy" wrote: in an adjacent column =if(left(E2,3)="www",if(right(E2,1)*11,na(),"")," ") then drag fill down the column select the column with the formula and do Edit=Goto=Special, select Formulas and uncheck everything except errors. Click OK Do Edit=Delete and select entire row delete this column. It takes longer to describe than it does to do. -- Regards, Tom Ogilvy "Dwoeltje" wrote: I need to create what I thought would be a very simple macro; select some rows, delete them, and then do a File - Save As, and save the file out as a tab delimited ascii text file. Simple, right? That's what I thought. Except that I need to select those rows based on certain criteria. The spreadsheet in question has seven columns it is using. The first row in the spreadsheet contains the column names. The fifth column is called Server Tested (it is column E). The cells in this column can have only one of four possible values; www1 or www2 or www3 or www5. What I need to do is to select all the rows that have a cell, in the 5th column of the row, that has a value of www2 or www3 or www5 (all rows where that cell has a value of www1 I want to be left alone), and then delete those rows. Now, if this were a database and I was using SQL, I might use something similar to this: Delete * from tablename (where ([dbname.tablename.fieldname] = "www2") or ([dbname.tablename.fieldname] = "www3" or ([dbname.tablename.fieldname] = "www5")) That might not be exactly correct syntax, but you get the idea. But I can't find anything anwhere in the Excel menu structure that will even let me select rows based specific criteria. I was able to get Excel to select specific cells based on specific criteria, but it only selected the cell, not the entire row that the cell was in. And then it did offer me any way to do a delete after selecting the cells (even if it had, it wouldn't have done me any good because I needed to delete the whole row, not just that specific cell). Can you tell me how to do what I need to do? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows based on a cell value | Excel Programming | |||
Macro Help for Deleting Blank Rows & Clearing Cell Contents | Excel Programming | |||
how to select a Row based on contents of a Cell | Excel Programming | |||
Deleting rows dependent upon cell contents | Excel Programming | |||
select a cell based on A1 contents | Excel Programming |