Deleting a row

[MarkN]

Hello,

I have a macro that finds a specific value in column one, cuts the entire
row and then pastes it to a specified location within the same sheet.

My only problem is that I need to delete the row from where I have cut the
record. The macro is essentially a report formatter and so there are
deliberate blank lines through the report. How can I cut a row of
information, paste it and then go back to the same row to delete it?

--
Thanks in advance,
MarkN

[avveerkar]

If the row you copied from was say 10 then why not

Rows("10:10").Select
Selection.Delete

This should work, unless you have something else in your mind.

A V Veerkar

[broro183]

Hi Mark,
Have a play with incorporating the following into your code:

(Paste following 2 lines at top of macro)
Dim RowToCopy As Long
Dim RowToPasteTo As Long

(Paste the following into the copy & paste area of the macro)
RowToCopy = ActiveCell.Row 'change as needed
RowToPasteTo = 23 'change as needed
Rows(RowToCopy).Copy Range("A" & RowToPasteTo)
Rows(RowToCopy).Delete

Note, the lines "RowToCopy = "... & "RowToPasteTo ="... need to be changed to suit your needs. This code results in the row immediately below the copied row becoming the active one after the copied one has been deleted.

hth,
Rob Brockett
NZ
always learning & the best way to learn is to experience...

[MarkN]

Hi Rob,

Thanks very much, after some tweaking your suggestion worked well.
--
Thanks again,
MarkN


"broro183" wrote:

>
> Hi Mark,
> Have a play with incorporating the following into your code:
>
> (Paste following 2 lines at top of macro)
> Dim RowToCopy As Long
> Dim RowToPasteTo As Long
>
> (Paste the following into the copy & paste area of the macro)
> RowToCopy = ActiveCell.Row 'change as needed
> RowToPasteTo = 23 'change as needed
> Rows(RowToCopy).Copy Range("A" & RowToPasteTo)
> Rows(RowToCopy).Delete
>
> Note, the lines "RowToCopy = "... & "RowToPasteTo ="... need to be
> changed to suit your needs. This code results in the row immediately
> below the copied row becoming the active one after the copied one has
> been deleted.
>
> hth,
> Rob Brockett
> NZ
> always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=508852
>
>

[broro183]

Hi Mark,
Pleased I could help :-)

Rob Brockett
NZ
always learning & the best way to learn is to experience...