Delete Rows question
 

I wish to get the following standard row-deletion code to be tweaked to
delete (not an enrireRow) but only rows across 3 columns - Columns A to
C.
I resorted to RESIZE method without success.

Sub DeleteRowsByCollection()

Dim c as Range
Dim rng as Range

For each c in Range("a1:a" & [a65536].End(xlUp).row)
If c.value = c.Offset(1,0).value Then
If rng Is Nothing Then
set rng = c.Resize(0,3) ========Application-defined or object-defined
error
Else
set rng = Union(rng, c.Resize(0,3))
End if
End if
Next

If not rng is Nothing then
rng.Delete
End if

End Sub

Deferring the RESIZING and applying it to the DELETE command as in
*rng.Resize(0,3).Delete * generates the same error.

TIA

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=522055

Delete Rows question
 

not sure exactly what you want. but if you just want to delete the 3 columns and
the rng variable holds the values, then

rng.Delete Shift:=xlToLeft

will delete them and shift whatever is to the right of the deletion to the left

--


Gary


"Myles" wrote in message
...

I wish to get the following standard row-deletion code to be tweaked to
delete (not an enrireRow) but only rows across 3 columns - Columns A to
C.
I resorted to RESIZE method without success.

Sub DeleteRowsByCollection()

Dim c as Range
Dim rng as Range

For each c in Range("a1:a" & [a65536].End(xlUp).row)
If c.value = c.Offset(1,0).value Then
If rng Is Nothing Then
set rng = c.Resize(0,3) ========Application-defined or object-defined
error
Else
set rng = Union(rng, c.Resize(0,3))
End if
End if
Next

If not rng is Nothing then
rng.Delete
End if

End Sub

Deferring the RESIZING and applying it to the DELETE command as in
*rng.Resize(0,3).Delete * generates the same error.

TIA

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=522055

Delete Rows question
 

sorry, if you're deleting rows


rng.Delete Shift:=xlUp

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
not sure exactly what you want. but if you just want to delete the 3 columns
and the rng variable holds the values, then

rng.Delete Shift:=xlToLeft

will delete them and shift whatever is to the right of the deletion to the
left

--


Gary


"Myles" wrote in message
...

I wish to get the following standard row-deletion code to be tweaked to
delete (not an enrireRow) but only rows across 3 columns - Columns A to
C.
I resorted to RESIZE method without success.

Sub DeleteRowsByCollection()

Dim c as Range
Dim rng as Range

For each c in Range("a1:a" & [a65536].End(xlUp).row)
If c.value = c.Offset(1,0).value Then
If rng Is Nothing Then
set rng = c.Resize(0,3) ========Application-defined or object-defined
error
Else
set rng = Union(rng, c.Resize(0,3))
End if
End if
Next

If not rng is Nothing then
rng.Delete
End if

End Sub

Deferring the RESIZING and applying it to the DELETE command as in
*rng.Resize(0,3).Delete * generates the same error.

TIA

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=522055

Delete Rows question
 

Thanks Gary.

But the issue is not addresed by the directional shift keys upon
deleting.
Altering *rng.Delete* to *rng.Delete Shift:=xlUP* (and omitting the
RESIZE qualification) willl leave columns B and C intact and that is
not desired.

myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=522055