Understanding range objects used with .delete/.resize/.offset methods

I've spent an embarrassing amount of time and frustration simply attempting to delete a cell. Please help me understand the scope of the problem statement in addition to any common techniques for using the subject methods efficiently and concisely.

A specific problem that highlights my ignorance is described below.

I will set a range to a cell and attempt to delete it and it consistently deletes the cell just below a given row. I will even go as far as to prove that the cell is set to a given row and column with a MsgBox ("Column: " & rng_todelete.Offset(ColumnOffset:=i).Column & vbCr & "Row: " & rng_todelete.Offset(ColumnOffset:=i).row) line of code just before rng_todelete.Delete and I still delete the cell just below rng_todelete.Delete is located.

The loop encompassing that Set rng_todelete = rng.Offset(0, i) line is also unnecessary. I can resize that to delete 5 rows with an expression similar to rng.Offset(some arg.).Resize( ColumnSize:= 5).

Please help me understand .delete/.resize/.offset methods. I'm trying very hard to avoid .selection/.select methods.

All this code is trying to do is compare 5 consecutive cells of code and delete duplicate sets of 5 identical consecutive cells across a few hundred columns. This should be very simple.

Please Login or Register  to view this content.

Thank you

The particular workbook I'm operating on may be found here: http://www.excelforum.com/excel-prog...uplicates.html

Can you explain in words and sentences what you're trying to do, without reference to code?

EDIT: It is usually true that in deleting things, you work from bottom to top. Otherwise references get very confusing.

Sorry for the confusion and thanks for the response. I'll break it down...


First, I want to /delete one cell using a range object and a .delete method.

I step through this simple code on a workbook with content in rows 4 & 5 only and I get an output from MsgBox() describing the row/column:
Column: 1
Row: 4
...which verifies rng is set to column 1 and row 4. But when the very next line executes: rng.delete, Column 1, row 5 is deleted.

Please Login or Register  to view this content.

Recent Edit: I get the same result if I replace rng.Delete. with ws.Cells(rng.row, rng.Column).Delete

I'm not looking at your code, I'm looking for a lucid explanation of what you're trying to do.

I'm trying to delete a cell using a range object and .delete method.

Could you please elaborate on why/how references get confusing when working from top to bottom? That may be a clue...

Please Login or Register  to view this content.

Does that do what you would expect?

shg,

I didn't understand how Range.Delete worked until now and it's now clear that when I delete ranges, I want to start from the bottom and work my way up.

I'll work through the rest of my problem, but now my issues will probably be as obvious as... well -- "Bob's your uncle".

Thank you very much for your help.

Joe

Bingo.

You're welcome.