If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automatic Deletion of Rows
Dear Experts,
I wish to : 1. Select columns containing a certain value ( here 0) 2. Then delete the ROWs with that valie automatically. I found the command for automatic search, but how to delete? Thank you. Best Regards |
#2
|
|||
|
|||
Hi
this code will look for a value in column A and delete the rows that the value appears in --- Sub DelRows() Dim c As Range strfind = InputBox("enter value to find and delete") With Worksheets(1).Range("A:A") Do Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With End Sub --- to use the code, right mouse click on a sheet tab, choose view code, this will display the vbe window, choose insert / module from the menu and then copy & paste this code on the right hand side of the screen - if you want the code to work on columns other than A, change the Range("A:A") to the appropriate columns. Then use ALT & F11 to switch back to your workbook, choose tools / macro / macros, find DelRows and press RUN Note: it is always a good idea to try something new on a copy of your workbook first ... just to ensure that the results are what you want. - -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "ai18ma" wrote in message ... Dear Experts, I wish to : 1. Select columns containing a certain value ( here 0) 2. Then delete the ROWs with that valie automatically. I found the command for automatic search, but how to delete? Thank you. Best Regards |
#3
|
|||
|
|||
Dear JulieD
Many thanks, it works! However, it couldn't resolve "inputbox", maybe because I am using a German version of Excel. I just replaced strfind with the value to search for, and it did it! Vielen herzlichen Dank! "JulieD" wrote: Hi this code will look for a value in column A and delete the rows that the value appears in --- Sub DelRows() Dim c As Range strfind = InputBox("enter value to find and delete") With Worksheets(1).Range("A:A") Do Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With End Sub --- to use the code, right mouse click on a sheet tab, choose view code, this will display the vbe window, choose insert / module from the menu and then copy & paste this code on the right hand side of the screen - if you want the code to work on columns other than A, change the Range("A:A") to the appropriate columns. Then use ALT & F11 to switch back to your workbook, choose tools / macro / macros, find DelRows and press RUN Note: it is always a good idea to try something new on a copy of your workbook first ... just to ensure that the results are what you want. - -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "ai18ma" wrote in message ... Dear Experts, I wish to : 1. Select columns containing a certain value ( here 0) 2. Then delete the ROWs with that valie automatically. I found the command for automatic search, but how to delete? Thank you. Best Regards |
#4
|
|||
|
|||
you're welcome - glad it works ... not sure what the German equivalent to
INPUTBOX is ... luckily i only have to work in English - i think Excel would be 1000 times harder if i had to deal with language differences (i have enought trouble remembering to spell things the "american" way) -- Cheers JulieD PS what part of germany, i spent 7 months living & working in Dusseldorf half a life time ago "ai18ma" wrote in message ... Dear JulieD Many thanks, it works! However, it couldn't resolve "inputbox", maybe because I am using a German version of Excel. I just replaced strfind with the value to search for, and it did it! Vielen herzlichen Dank! "JulieD" wrote: Hi this code will look for a value in column A and delete the rows that the value appears in --- Sub DelRows() Dim c As Range strfind = InputBox("enter value to find and delete") With Worksheets(1).Range("A:A") Do Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With End Sub --- to use the code, right mouse click on a sheet tab, choose view code, this will display the vbe window, choose insert / module from the menu and then copy & paste this code on the right hand side of the screen - if you want the code to work on columns other than A, change the Range("A:A") to the appropriate columns. Then use ALT & F11 to switch back to your workbook, choose tools / macro / macros, find DelRows and press RUN Note: it is always a good idea to try something new on a copy of your workbook first ... just to ensure that the results are what you want. - -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "ai18ma" wrote in message ... Dear Experts, I wish to : 1. Select columns containing a certain value ( here 0) 2. Then delete the ROWs with that valie automatically. I found the command for automatic search, but how to delete? Thank you. Best Regards |
#5
|
|||
|
|||
I live in Cologne, about 30 miles south of Duesseldorf, a very nice place!
How did you like it here? Kinda hard getting along with Germans, ey?! Regards "JulieD" wrote: you're welcome - glad it works ... not sure what the German equivalent to INPUTBOX is ... luckily i only have to work in English - i think Excel would be 1000 times harder if i had to deal with language differences (i have enought trouble remembering to spell things the "american" way) -- Cheers JulieD PS what part of germany, i spent 7 months living & working in Dusseldorf half a life time ago "ai18ma" wrote in message ... Dear JulieD Many thanks, it works! However, it couldn't resolve "inputbox", maybe because I am using a German version of Excel. I just replaced strfind with the value to search for, and it did it! Vielen herzlichen Dank! "JulieD" wrote: Hi this code will look for a value in column A and delete the rows that the value appears in --- Sub DelRows() Dim c As Range strfind = InputBox("enter value to find and delete") With Worksheets(1).Range("A:A") Do Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With End Sub --- to use the code, right mouse click on a sheet tab, choose view code, this will display the vbe window, choose insert / module from the menu and then copy & paste this code on the right hand side of the screen - if you want the code to work on columns other than A, change the Range("A:A") to the appropriate columns. Then use ALT & F11 to switch back to your workbook, choose tools / macro / macros, find DelRows and press RUN Note: it is always a good idea to try something new on a copy of your workbook first ... just to ensure that the results are what you want. - -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "ai18ma" wrote in message ... Dear Experts, I wish to : 1. Select columns containing a certain value ( here 0) 2. Then delete the ROWs with that valie automatically. I found the command for automatic search, but how to delete? Thank you. Best Regards |
#6
|
|||
|
|||
actually i didn't have much problem at all - would have chosen to live in
germany if i didn't end up back in australia. -- Cheers JulieD "ai18ma" wrote in message ... I live in Cologne, about 30 miles south of Duesseldorf, a very nice place! How did you like it here? Kinda hard getting along with Germans, ey?! Regards "JulieD" wrote: you're welcome - glad it works ... not sure what the German equivalent to INPUTBOX is ... luckily i only have to work in English - i think Excel would be 1000 times harder if i had to deal with language differences (i have enought trouble remembering to spell things the "american" way) -- Cheers JulieD PS what part of germany, i spent 7 months living & working in Dusseldorf half a life time ago "ai18ma" wrote in message ... Dear JulieD Many thanks, it works! However, it couldn't resolve "inputbox", maybe because I am using a German version of Excel. I just replaced strfind with the value to search for, and it did it! Vielen herzlichen Dank! "JulieD" wrote: Hi this code will look for a value in column A and delete the rows that the value appears in --- Sub DelRows() Dim c As Range strfind = InputBox("enter value to find and delete") With Worksheets(1).Range("A:A") Do Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With End Sub --- to use the code, right mouse click on a sheet tab, choose view code, this will display the vbe window, choose insert / module from the menu and then copy & paste this code on the right hand side of the screen - if you want the code to work on columns other than A, change the Range("A:A") to the appropriate columns. Then use ALT & F11 to switch back to your workbook, choose tools / macro / macros, find DelRows and press RUN Note: it is always a good idea to try something new on a copy of your workbook first ... just to ensure that the results are what you want. - -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "ai18ma" wrote in message ... Dear Experts, I wish to : 1. Select columns containing a certain value ( here 0) 2. Then delete the ROWs with that valie automatically. I found the command for automatic search, but how to delete? Thank you. Best Regards |
#7
|
|||
|
|||
Automatic Deletion of Rows
Thank you JulieD the code came very handy!!
:* can you pl. modify it so that it can search with wild cards too.. Thanks in advance. "JulieD" wrote: Hi this code will look for a value in column A and delete the rows that the value appears in --- Sub DelRows() Dim c As Range strfind = InputBox("enter value to find and delete") With Worksheets(1).Range("A:A") Do Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With End Sub --- to use the code, right mouse click on a sheet tab, choose view code, this will display the vbe window, choose insert / module from the menu and then copy & paste this code on the right hand side of the screen - if you want the code to work on columns other than A, change the Range("A:A") to the appropriate columns. Then use ALT & F11 to switch back to your workbook, choose tools / macro / macros, find DelRows and press RUN Note: it is always a good idea to try something new on a copy of your workbook first ... just to ensure that the results are what you want. - -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "ai18ma" wrote in message ... Dear Experts, I wish to : 1. Select columns containing a certain value ( here 0) 2. Then delete the ROWs with that valie automatically. I found the command for automatic search, but how to delete? Thank you. Best Regards |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Row selections by row # OR by even/odd rows in another spreadsheet | Tom | General Discussion | 0 | February 9th, 2005 04:03 PM |
Adding Rows to Master Sheet | Excel Newbie | New Users | 1 | December 23rd, 2004 10:56 PM |
Automatic hiding of rows based on variables | Bob | Worksheet Functions | 2 | August 13th, 2004 07:54 PM |
Automatic removal of rows | Peter | General Discussion | 1 | August 4th, 2004 02:01 PM |
Pivot Table Too Many Rows Or Colums | Joe | Worksheet Functions | 0 | October 9th, 2003 05:17 PM |