Home |
Search |
Today's Posts |
#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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Deletion of rows where a value is satisfied | Excel Worksheet Functions | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) | |||
Row selections by row # OR by even/odd rows in another spreadsheet | Excel Discussion (Misc queries) | |||
Adding Rows to Master Sheet | New Users to Excel |