Need to delete rows relative to selection

Hi all,

I'm sure that this is quite simple, but I haven't done much coding for quite
some time and need a quick fix. The following is my base code:

'
Rows("914:1127").Select
Selection.Delete Shift:=xlUp
Rows("914:914").Select
Selection.Insert Shift:=xlDown
End Sub

Now, obviously this macro will only affect rows 914 - 1127. What I need to
happen is for the macro to delete the next 213 rows, including the one row
that I will select. I will then require a new row inserted into the selected
row. Basically I just need to know how to make a "relative" reference in the
code based on the row I will physically select.

Thanks in advance for any help provided.

Suzanne

Hello Suzanne,

If I understand your post correcrtly, you want to select a cell and have that cell's row and the next 213 rows below it deleted. It appaears that in your code your are deleteing the entire row (all the columns). This code sample illustrates how to do that in a relative way.

Addressing Relative to Active cell:
FirstRow = ActiveCell.Row
LastRow = 213 + FirstRow
Addx = "$" & FirstRow & ":$" & LastRow

ActiveSheet.Range(Addx).Delete (xlShiftUp)

Sincerely,
Leith Ross

If you run this with the required row selected then it will work.....

Rows(ActiveCell.Row & ":" & ActiveCell.Row + 212).Delete
Rows(ActiveCell.Row).Insert

--
Cheers
Nigel



"ratchick" <ratchick@discussions.microsoft.com> wrote in message
news:0CB997D9-886F-48D8-BFEF-CC371175BB01@microsoft.com...
> Hi all,
>
> I'm sure that this is quite simple, but I haven't done much coding for
quite
> some time and need a quick fix. The following is my base code:
>
> '
> Rows("914:1127").Select
> Selection.Delete Shift:=xlUp
> Rows("914:914").Select
> Selection.Insert Shift:=xlDown
> End Sub
>
> Now, obviously this macro will only affect rows 914 - 1127. What I need
to
> happen is for the macro to delete the next 213 rows, including the one row
> that I will select. I will then require a new row inserted into the
selected
> row. Basically I just need to know how to make a "relative" reference in
the
> code based on the row I will physically select.
>
> Thanks in advance for any help provided.
>
> Suzanne

ActiveCell.Resize(213).EntireRow.Delete
ActiveCell.EntireRow.Insert

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ratchick" <ratchick@discussions.microsoft.com> wrote in message
news:0CB997D9-886F-48D8-BFEF-CC371175BB01@microsoft.com...
> Hi all,
>
> I'm sure that this is quite simple, but I haven't done much coding for
quite
> some time and need a quick fix. The following is my base code:
>
> '
> Rows("914:1127").Select
> Selection.Delete Shift:=xlUp
> Rows("914:914").Select
> Selection.Insert Shift:=xlDown
> End Sub
>
> Now, obviously this macro will only affect rows 914 - 1127. What I need
to
> happen is for the macro to delete the next 213 rows, including the one row
> that I will select. I will then require a new row inserted into the
selected
> row. Basically I just need to know how to make a "relative" reference in
the
> code based on the row I will physically select.
>
> Thanks in advance for any help provided.
>
> Suzanne

Nigel, Leith and Bob,

Thank you all very much for your help. All three solutions work out well
for me. This will save me quite a bit of time.

Regards,
Suzanne

"ratchick" wrote:

> Hi all,
>
> I'm sure that this is quite simple, but I haven't done much coding for quite
> some time and need a quick fix. The following is my base code:
>
> '
> Rows("914:1127").Select
> Selection.Delete Shift:=xlUp
> Rows("914:914").Select
> Selection.Insert Shift:=xlDown
> End Sub
>
> Now, obviously this macro will only affect rows 914 - 1127. What I need to
> happen is for the macro to delete the next 213 rows, including the one row
> that I will select. I will then require a new row inserted into the selected
> row. Basically I just need to know how to make a "relative" reference in the
> code based on the row I will physically select.
>
> Thanks in advance for any help provided.
>
> Suzanne