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.
Sub Deleterows()
Dim ws As Worksheet
Dim dM As Dictionary
Dim row As Long, col As Long
Dim rng As Range, rng_row As Range, rng_todelete As Range
Dim i As Integer
Dim LongKey As String
Set dM = New Dictionary
Set ws = ThisWorkbook.Worksheets("Temp5")
LongKey = Empty
For Each rng_row In ws.UsedRange.Rows
For Each rng In rng_row.Cells
row = rng.row
col = rng.Column
If col Mod 5 = 0 Then
LongKey = LongKey & rng.Value
If dM.Exists(LongKey) Then
For i = 0 To -4 Step -1
Set rng_todelete = rng.Offset(0, i)
MsgBox ("Column: " & rng.Offset(ColumnOffset:=i).Column & vbCr & "Row: " & rng.Offset(ColumnOffset:=i).row)
MsgBox ("Column: " & rng_todelete.Offset(ColumnOffset:=i).Column & vbCr & "Row: " & rng_todelete.Offset(ColumnOffset:=i).row)
rng_todelete.Delete
Next i
Else
dM.Item(LongKey) = rng.Address
LongKey = Empty
End If
Else
LongKey = rng.Value & LongKey
End If
Next rng
Next rng_row
End Sub
Thank you
Bookmarks