Code not cooperating

[gheyman]

I have spent the last hour looking at this to see where the problem is.

I need to look across row 5, starting in column 6 (F) and if the its not Balnk look at column 5 and see if its value is not null. If it is Null, then delete whats in column 5

Now column 5 typically has values further to the right than column 6 does. Those are the cells I am trying to clear

Remove unneeded Dates in Row 5
'If the cell in row 6 is blank delete data in row 5
Dim i As Long
Dim LastCol As Long

With Sheets("Monthly Allocation Report")
LastCol = .Cells(5, .Columns.Count).End(xlUp).Column
End With

With Sheets("Monthly Allocation Report")
For i = 5 To LastCol
If Len(.Cells(6, i).Value) > 1 Then
.Cells(5, i).Clear
End If
Next i
End With

 

[mole999]

is this right ?

LastCol = .Cells(5, .Columns.Count).End(xlUp).Column

i know xlup is good for rows ?

 

[VoG]

Yes, it should be

LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column

 

[gheyman]

I tried changing that to End(xlToRight). But it didnt fix the problem.

I have data in Cells F5 through BB5
and then data in Cells F6 through AN6

when I run the code it is deleting data in cells N5 through AN5. Its leaving in all the data in column 5 after cell AN5.

I am baffled. And I am stuck!

 

[gheyman]

Also tried the LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column

It delets some of the data in row 5 but not all of it. it only deletes it from N through AN. I dont see a logical reason for that. the cells after AN have data in rows 5 and 6. Why this is deleteing only from that range is very very stranges to me!!

 

[VoG]

Try looping backwards

For i = lastcol To 5 Step -1
    If Len(.Cells(6, i).Value) > 1 Then
        .Cells(5, i).Clear
    End If
Next i