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.

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.

try
Sub cutpasterow1()
With Rows(5)
.Copy Sheets("Sheet8").Rows(27)
.Delete Shift:=xlUp
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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.

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.

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.

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.

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.

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.

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.

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.