Quirky Code
 

This code kinda works for me. There is something off about it though.

Dim lastrow As Long, o As Long
Dim cell As Range

lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For i = lastrow To 1 Step -1
Set cell = Cells(i, "I")
If IsNumeric(cell) Then
If Not (cell = 1 Or cell = 7 Or cell = D) Then
cell.Offset(0, -4).Resize(1, 6).Delete Shift:=xlToLeft
End If
End If
Next

It is intended to do the following:
Select Column I. If there is a 1, 7, or D in that column, then leave
it. If there is anything else in the column then it is to select Columns E
through J within that row only and delete that part of the row, then shift
everything from the right over into its place.

Now I have the formula repreating 5 times or so to clean up the data, it
works for the most part, BUT there are a small handful or rows it doesn't
work for. It's not the same rows every time either, so I'm not sure what is
wrong with it. Some rows it will just keep deleting, regardless of the
numbers/letters in the cell. What am I doing wrong? Thanks!!!!

Quirky Code
 

If Not (cell = 1 Or cell = 7 Or cell = "D") Then

You forgot the quotes, so VB compares cells's value with Variant D

HTH
--
AP

"bodhisatvaofboogie" a écrit
dans le message de news:
...
This code kinda works for me. There is something off about it though.

Dim lastrow As Long, o As Long
Dim cell As Range

lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For i = lastrow To 1 Step -1
Set cell = Cells(i, "I")
If IsNumeric(cell) Then
If Not (cell = 1 Or cell = 7 Or cell = D) Then
cell.Offset(0, -4).Resize(1, 6).Delete Shift:=xlToLeft
End If
End If
Next

It is intended to do the following:
Select Column I. If there is a 1, 7, or D in that column, then leave
it. If there is anything else in the column then it is to select Columns
E
through J within that row only and delete that part of the row, then shift
everything from the right over into its place.

Now I have the formula repreating 5 times or so to clean up the data, it
works for the most part, BUT there are a small handful or rows it doesn't
work for. It's not the same rows every time either, so I'm not sure what
is
wrong with it. Some rows it will just keep deleting, regardless of the
numbers/letters in the cell. What am I doing wrong? Thanks!!!!

Quirky Code
 

The first thing I noticed was

If IsNumeric(cell) Then .... If Not (cell = 1 Or cell = 7 Or cell = D)
Then

If a cell is numeric it can't contain a D

The second thing was

cell = D I think you mean cell = "D"

So maybe this is what your lokking for

Dim lastrow As Long, o As Long
Dim sCell As String

lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For i = lastrow To 1 Step -1
sCell = Trim(Cells(i, "I").Value)
If Not (sCell = "1" Or sCell = "7" Or sCell = "D") Then
cell.Offset(0, -4).Resize(1, 6).Delete Shift:=xlToLeft
End If
Next


--
Kaak
------------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
View this thread: http://www.excelforum.com/showthread...hreadid=555526