Quirky Code

[bodhisatvaofboogie]

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!!!!

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de news:
[email protected]...
> 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!!!!

[Kaak]

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