Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
Could anyone help with a Macro to delete a row.
My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
You should always post YOUR coding efforts' for comments and suggestions.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
I don't understand what you want to do. Your code:
ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
Otto, this code leaves a blank row every time I do it. You are correct that
it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
Your line:
ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
That is correct however what you are referring to is on 2 different
worksheets. The Macro button is on a sheet called All Electrical Resp Work < 2010, that is were the data is 'cut' from. The macro then goes to sheet 'Not SD Work' were it pastes the data. I have then added a line to insert a new row on worksheet 'Not SD Work' ready for the next data to be inserted. The rest of the code just sorts the data. The macro then goes back to the front sheet All Electrical Resp Work < 2010 were the Macro ends. The only row deletion is down to ActiveCell.EntireRow.Cut. at the start, hence I am left with an empty row. My question is, how do I delete that row using a macro. "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
I am obviously quite an amateur at this. I'm not sure how I enter or use the
code than Don gave me "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
ActiveCell.EntireRow.Delete
That's all you need to delete the row. By this I mean not only the contents of the row, but the entire row, contents and all. Otto "Woodi2" wrote in message ... That is correct however what you are referring to is on 2 different worksheets. The Macro button is on a sheet called All Electrical Resp Work < 2010, that is were the data is 'cut' from. The macro then goes to sheet 'Not SD Work' were it pastes the data. I have then added a line to insert a new row on worksheet 'Not SD Work' ready for the next data to be inserted. The rest of the code just sorts the data. The macro then goes back to the front sheet All Electrical Resp Work < 2010 were the Macro ends. The only row deletion is down to ActiveCell.EntireRow.Cut. at the start, hence I am left with an empty row. My question is, how do I delete that row using a macro. "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
You must first get to the VBE (Visual Basic Editor). You do this by holding
the Ctrl key down and hitting the F11 key. You should see a narrow pane at the left and a wider pane at the right. If there is no narrow pane at the left, click on View - Project Explorer. In the left pane, find the name of your file. Click on it. At the top, click on Insert - Module. Paste Don's code into that module that appears in the large right pane. Note the name of the macro Don wrote, "cutpasterow1". "X" out of the VBE. To run the macro, click on Tools - Macro - Macros. You will see a box with all the macros available in all the open workbooks. Find Don's macro. Click on it and click "Run". HTH Otto "Woodi2" wrote in message ... I am obviously quite an amateur at this. I'm not sure how I enter or use the code than Don gave me "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
Typo patrol<g
CTRL + F11 inserts a new Excel 4.0 macro sheet. Alt + F11 gets you the VBE window. Gord Dibben MS Excel MVP On Sat, 17 Nov 2007 22:45:20 -0500, "Otto Moehrbach" wrote: You must first get to the VBE (Visual Basic Editor). You do this by holding the Ctrl key down and hitting the F11 key. You should see a narrow pane at the left and a wider pane at the right. If there is no narrow pane at the left, click on View - Project Explorer. In the left pane, find the name of your file. Click on it. At the top, click on Insert - Module. Paste Don's code into that module that appears in the large right pane. Note the name of the macro Don wrote, "cutpasterow1". "X" out of the VBE. To run the macro, click on Tools - Macro - Macros. You will see a box with all the macros available in all the open workbooks. Find Don's macro. Click on it and click "Run". HTH Otto "Woodi2" wrote in message ... I am obviously quite an amateur at this. I'm not sure how I enter or use the code than Don gave me "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
The typo patrol is on the ball. Thanks Gord. Otto
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... Typo patrol<g CTRL + F11 inserts a new Excel 4.0 macro sheet. Alt + F11 gets you the VBE window. Gord Dibben MS Excel MVP On Sat, 17 Nov 2007 22:45:20 -0500, "Otto Moehrbach" wrote: You must first get to the VBE (Visual Basic Editor). You do this by holding the Ctrl key down and hitting the F11 key. You should see a narrow pane at the left and a wider pane at the right. If there is no narrow pane at the left, click on View - Project Explorer. In the left pane, find the name of your file. Click on it. At the top, click on Insert - Module. Paste Don's code into that module that appears in the large right pane. Note the name of the macro Don wrote, "cutpasterow1". "X" out of the VBE. To run the macro, click on Tools - Macro - Macros. You will see a box with all the macros available in all the open workbooks. Find Don's macro. Click on it and click "Run". HTH Otto "Woodi2" wrote in message ... I am obviously quite an amateur at this. I'm not sure how I enter or use the code than Don gave me "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
We gotta watch each other Otto.
Without the double-checks I think people would eventually quit coming here for our dysfunctional advice< My chell-specker never gets used which leads to some interesting and misleading suggestions on occasion. Gord On Sun, 18 Nov 2007 11:50:13 -0500, "Otto Moehrbach" wrote: The typo patrol is on the ball. Thanks Gord. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Typo patrol<g CTRL + F11 inserts a new Excel 4.0 macro sheet. Alt + F11 gets you the VBE window. Gord Dibben MS Excel MVP On Sat, 17 Nov 2007 22:45:20 -0500, "Otto Moehrbach" wrote: You must first get to the VBE (Visual Basic Editor). You do this by holding the Ctrl key down and hitting the F11 key. You should see a narrow pane at the left and a wider pane at the right. If there is no narrow pane at the left, click on View - Project Explorer. In the left pane, find the name of your file. Click on it. At the top, click on Insert - Module. Paste Don's code into that module that appears in the large right pane. Note the name of the macro Don wrote, "cutpasterow1". "X" out of the VBE. To run the macro, click on Tools - Macro - Macros. You will see a box with all the macros available in all the open workbooks. Find Don's macro. Click on it and click "Run". HTH Otto "Woodi2" wrote in message ... I am obviously quite an amateur at this. I'm not sure how I enter or use the code than Don gave me "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to delete row
I'm an old engineer and if I didn't have the chell-specker I would be
doomed. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message ... We gotta watch each other Otto. Without the double-checks I think people would eventually quit coming here for our dysfunctional advice< My chell-specker never gets used which leads to some interesting and misleading suggestions on occasion. Gord On Sun, 18 Nov 2007 11:50:13 -0500, "Otto Moehrbach" wrote: The typo patrol is on the ball. Thanks Gord. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message . .. Typo patrol<g CTRL + F11 inserts a new Excel 4.0 macro sheet. Alt + F11 gets you the VBE window. Gord Dibben MS Excel MVP On Sat, 17 Nov 2007 22:45:20 -0500, "Otto Moehrbach" wrote: You must first get to the VBE (Visual Basic Editor). You do this by holding the Ctrl key down and hitting the F11 key. You should see a narrow pane at the left and a wider pane at the right. If there is no narrow pane at the left, click on View - Project Explorer. In the left pane, find the name of your file. Click on it. At the top, click on Insert - Module. Paste Don's code into that module that appears in the large right pane. Note the name of the macro Don wrote, "cutpasterow1". "X" out of the VBE. To run the macro, click on Tools - Macro - Macros. You will see a box with all the macros available in all the open workbooks. Find Don's macro. Click on it and click "Run". HTH Otto "Woodi2" wrote in message ... I am obviously quite an amateur at this. I'm not sure how I enter or use the code than Don gave me "Otto Moehrbach" wrote: Your line: ActiveCell.EntireRow.Cut cuts the contents of the row. When you paste those contents you end up with a blank row. Your line: Selection.Insert Shift:=xlDown inserts another blank row. The code that Don gave you should do what you want. Otto "Woodi2" wrote in message ... Otto, this code leaves a blank row every time I do it. You are correct that it deletes the row of the active cell but it does not move everything up. Here is the complete code I have ActiveCell.EntireRow.Cut Sheets("Not SD Work").Select Rows("3:3").Select ActiveSheet.Paste Rows("3:3").Select Selection.Insert Shift:=xlDown Range("A4:Q2500").Select Selection.Sort Key1:=Range("L4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select Sheets("All Electrical Resp Work < 2010").Select Application.CutCopyMode = False "Otto Moehrbach" wrote: I don't understand what you want to do. Your code: ActiveCell.EntireRow.Delete deletes the row of the active cell and moves everything below it up. What does this do that you don't want? HTH Otto "Woodi2" wrote in message ... Could anyone help with a Macro to delete a row. My spreadsheet contains infomation that I review and then 'cut' the data from one worksheet and paste it in a new worksheet. One of several different ones actually This leaves a blank row in the original worksheet. The code I use for this is ActiveCell.EntireRow.Delete I cannot specify the row to delete in the code as the sheet is filtered so I would like to delete the row I have removed the data from and shift the remining data up. i.e. Rows.Delete Shift:=xlUp. The only problem with this code is the entire sheet gets deleted. I have tried ActiveRow and ActiveRows but VBA doesnt appear to recognize this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete Macro | Excel Discussion (Misc queries) | |||
Cannot Delete Macro | New Users to Excel | |||
delete a macro that isn't in macro list | Excel Discussion (Misc queries) | |||
How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) |