Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete and Shift Cells up
Hi all,
I'm trying to create a macro that will start at the very first cell, I guess A1, and work it's way down, and will check the value of the cell. If the value of the cell is not equal to the word "Sales" then delete that row. but if it finds the word sales then stop the process. Also can I have this macro be able to autorun on the save of the doc? For instance if I save an email attchment that is an excel file will it run when I save the attachment without opening the file and then saving it? So to be a little more clear if I right-click on the excel attached file in the email and click save, can i have this macro run? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete and Shift Cells up
I think a big No to part 2. When you 'save' an email attachment you are
really just copying a file from a temp to a permanent folder so Excel does not know anything is happening to the file You could have the macro run when the file is opened with an 'open even' Private Sub Workbook_Open() run the code End Sub This code must go on the workBOOK module - right click the Excel log next to 'File' on the menu and use View code Do you have a question about coding the macro? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "matt" wrote in message ... Hi all, I'm trying to create a macro that will start at the very first cell, I guess A1, and work it's way down, and will check the value of the cell. If the value of the cell is not equal to the word "Sales" then delete that row. but if it finds the word sales then stop the process. Also can I have this macro be able to autorun on the save of the doc? For instance if I save an email attchment that is an excel file will it run when I save the attachment without opening the file and then saving it? So to be a little more clear if I right-click on the excel attached file in the email and click save, can i have this macro run? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete and Shift Cells up
Wow, thanks for your QUICK response. Yes would you be able to help me code
this. So it is like this, Start in cell A1 and go down(just column A is fine) search for the value in the cell to be < "Sales" Then delete row and shift up the rows. But If the value = "Sales" then break out of the loop. Thanks, "Bernard Liengme" wrote: I think a big No to part 2. When you 'save' an email attachment you are really just copying a file from a temp to a permanent folder so Excel does not know anything is happening to the file You could have the macro run when the file is opened with an 'open even' Private Sub Workbook_Open() run the code End Sub This code must go on the workBOOK module - right click the Excel log next to 'File' on the menu and use View code Do you have a question about coding the macro? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "matt" wrote in message ... Hi all, I'm trying to create a macro that will start at the very first cell, I guess A1, and work it's way down, and will check the value of the cell. If the value of the cell is not equal to the word "Sales" then delete that row. but if it finds the word sales then stop the process. Also can I have this macro be able to autorun on the save of the doc? For instance if I save an email attchment that is an excel file will it run when I save the attachment without opening the file and then saving it? So to be a little more clear if I right-click on the excel attached file in the email and click save, can i have this macro run? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete and Shift Cells up
This worked for me
Sub Exterminator() Range("A1").Select Range(Selection, Selection.End(xlDown)).Select mycount = Selection.Count For j = mycount To 1 Step -1 If Cells(j, 1).Value = "Sales" Then Exit Sub Else Rows(j).Select Selection.Delete Shift:=xlUp End If Next j End Sub But in VBA the entry "sales" is not equal to "Sales To stop at any entry with those letters; replace If Cells(j, 1).Value = "Sales" Then with If Ucase(Cells(j, 1).Value) = "SALES" Then best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "matt" wrote in message ... Wow, thanks for your QUICK response. Yes would you be able to help me code this. So it is like this, Start in cell A1 and go down(just column A is fine) search for the value in the cell to be < "Sales" Then delete row and shift up the rows. But If the value = "Sales" then break out of the loop. Thanks, "Bernard Liengme" wrote: I think a big No to part 2. When you 'save' an email attachment you are really just copying a file from a temp to a permanent folder so Excel does not know anything is happening to the file You could have the macro run when the file is opened with an 'open even' Private Sub Workbook_Open() run the code End Sub This code must go on the workBOOK module - right click the Excel log next to 'File' on the menu and use View code Do you have a question about coding the macro? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "matt" wrote in message ... Hi all, I'm trying to create a macro that will start at the very first cell, I guess A1, and work it's way down, and will check the value of the cell. If the value of the cell is not equal to the word "Sales" then delete that row. but if it finds the word sales then stop the process. Also can I have this macro be able to autorun on the save of the doc? For instance if I save an email attchment that is an excel file will it run when I save the attachment without opening the file and then saving it? So to be a little more clear if I right-click on the excel attached file in the and click save, can i have this macro run? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-ID and Delete/left shift cells when a cell contains text? | Excel Discussion (Misc queries) | |||
How to delete all the blanc cells in a worksheet and shift cells l | Excel Discussion (Misc queries) | |||
delete and shift up | Excel Programming | |||
delete coumns - shift non-blank cells message | Excel Discussion (Misc queries) | |||
Delete - Shift Cells UP problem | Excel Discussion (Misc queries) |