Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
Hi All,
I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
Consider Cell Z100:
Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
Hi Gary,
I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
You must insure that cell Z100 has a zero in it.
-- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi Gary, I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
something like
for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if cells(i,"a")=0 then rows(i).delete next i -- Don Guillett SalesAid Software "Dolphy" wrote in message ups.com... Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from the Excel worksheet: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi Gary, I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
Hi Gary,
I've tried running the macro, this time I've ensured that cell Z100 has a zero (0) in it and I'm getting the following error when I try to run the macro: Compile error: Expected: end of statement Rgds, Dolphy On May 28, 10:51 pm, Gary''s Student wrote: Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from the Excel worksheet: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi Gary, I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
You've changed Gary's Student's code.
Either recopy and paste it again or share the code you're using. Dolphy wrote: Hi Gary, I've tried running the macro, this time I've ensured that cell Z100 has a zero (0) in it and I'm getting the following error when I try to run the macro: Compile error: Expected: end of statement Rgds, Dolphy On May 28, 10:51 pm, Gary''s Student wrote: Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from the Excel worksheet: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi Gary, I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
I've used the below code and ensured that cell Z100 has a zero in it.
Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Rgds, Dolphy On Jun 4, 9:20 pm, Dave Peterson wrote: You've changed Gary's Student's code. Either recopy and paste it again or share the code you're using. Dolphywrote: Hi Gary, I've tried running the macro, this time I've ensured that cell Z100 has a zero (0) in it and I'm getting the following error when I try to run the macro: Compile error: Expected: end of statement Rgds, Dolphy On May 28, 10:51 pm, Gary''s Student wrote: Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from the Excel worksheet: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi Gary, I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
And you got that error message with this code.
Maybe you have a hidden character somewhere in your code. I'd delete the existing version (remove the whole module and create a new one???) and copy and paste from your post. I did that and you posted code worked fine. Dolphy wrote: I've used the below code and ensured that cell Z100 has a zero in it. Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Rgds, Dolphy On Jun 4, 9:20 pm, Dave Peterson wrote: You've changed Gary's Student's code. Either recopy and paste it again or share the code you're using. Dolphywrote: Hi Gary, I've tried running the macro, this time I've ensured that cell Z100 has a zero (0) in it and I'm getting the following error when I try to run the macro: Compile error: Expected: end of statement Rgds, Dolphy On May 28, 10:51 pm, Gary''s Student wrote: Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from the Excel worksheet: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi Gary, I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Easy Excel Question
I did not understand the original post:
Sub kill_row() Set rdel = Range("A65536") For Each r In ActiveSheet.UsedRange If Not IsEmpty(r) Then If r.Value = 0 Then Set rdel = Union(rdel, r) End If End If Next rdel.EntireRow.Delete End Sub -- Gary''s Student - gsnu200727 "Dolphy" wrote: I've tested the macro again. All it's doing is erase the row 100 if it has a zero in it. If it's working for you Dave, can you email it to me? Just to re-iterate: what I would like the macro to do is delete all rows if any of its cell have a zero in it. BTW, I appreciate all of the input provided. Rgds, Dolphy On Jun 5, 2:52 pm, Dave Peterson wrote: And you got that error message with this code. Maybe you have a hidden character somewhere in your code. I'd delete the existing version (remove the whole module and create a new one???) and copy and paste from your post. I did that and you posted code worked fine. Dolphy wrote: I've used the below code and ensured that cell Z100 has a zero in it. Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Rgds, Dolphy On Jun 4, 9:20 pm, Dave Peterson wrote: You've changed Gary's Student's code. Either recopy and paste it again or share the code you're using. Dolphywrote: Hi Gary, I've tried running the macro, this time I've ensured that cell Z100 has a zero (0) in it and I'm getting the following error when I try to run the macro: Compile error: Expected: end of statement Rgds, Dolphy On May 28, 10:51 pm, Gary''s Student wrote: Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from the Excel worksheet: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi Gary, I seem to be unable to use the below script. I'm using Office Xp (Excel 2002). I've copy and pasted the script using the below steps: On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. What am I doing wrong? Rgds, Dolphy On May 28, 10:26 am, Gary''s Student wrote: Consider Cell Z100: Sub kill_row() Set r = Range("Z100") If Not IsEmpty(r) Then If r.Value = 0 Then r.EntireRow.Delete End If End If End Sub Note we check that the cell is not empty and has a real zero in it. -- Gary''s Student - gsnu200725 "Dolphy" wrote: Hi All, I wanted to run a simple script/macro in Excel. All I wanted is a rule whereby if a cell has a value of 0 (zero) I would like to delete the row. Can someone please assist? TIA Dolphy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easy Excel formula question | Excel Discussion (Misc queries) | |||
*EASY* question! | New Users to Excel | |||
excel easy question !!!! | Setting up and Configuration of Excel | |||
new user with easy question? not easy for me | New Users to Excel | |||
easy question .... i think | Excel Discussion (Misc queries) |