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

Need to delete rows relative to selection
 

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

Need to delete rows relative to selection
 

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=487101

Need to delete rows relative to selection
 

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

--

HTH

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


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

Need to delete rows relative to selection
 

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