increment line index

[houde]

Hello!
Does anybody know how I can make the row index (i) to increment by one, once the cursor has reached the 101th column?
What I'm trying to do is to delete all cells that contain the value 99 and shift the contents to the left. This should be done in all cells from Cell(2,2) unitl Cell(476,101). This is what I came up with, and it works, except that it bugs when I try to add another loop to increment the line index... I would very, very, very much appreciate any help!!! Thank you

Sub fabTest()
'
' fabTest Macro
' Macro recorded 1/22/2007 by wyssfab
'

'
Dim i As Integer
Dim k As Integer
k = 2
i = 2
Do
Cells(i, k).Select
Do
If Cells(i, k).Value = 99 Then
Selection.Delete Shift:=xlToLeft
Else: Cells(i, k + 1).Select
End If
Loop Until Cells(i, k).Value <> 99
k = k + 1
Loop Until k = 101

End Sub

 

[SkipVought]

Hi,

When deleting rows or columns, loop from the greatest to the least otherwise you will loose your reference.

Skip,

_[red][/red]

 

[houde]

Hey,
That's a good tip, I'll try it out. I added another loop, and it now actually increments from line 17 to line 18, but then it bugs at the line "Else: Cells(i, k + 1).Select", and when I click on the k, it sais 256. I don't understand why it even goes to k=256, since I limited it to k=101...? Thank you so much for your help!


Sub fabTest()
'
' fabTest Macro
' Macro recorded 1/22/2007 by wyssfab
'

'
Dim i As Integer
Dim k As Integer
i = 17
k = 2
Do
Cells(i, k).Select
Do
Cells(i, k).Select
Do
If Cells(i, k).Value = 99 Then
Selection.Delete Shift:=xlToLeft
Else: Cells(i, k + 1).Select
End If
Loop Until Cells(i, k).Value <> 99
k = k + 1
Loop Until k = 101
i = i + 1
Loop Until i = 476

End Sub

 

[PHV]

Why not using For instead of Do ?
For i = 2 To 476
For k = 101 To 2 Step -1
If Cells(i, k).Value = 99 Then
Cells(i, k).Delete Shift:=xlToLeft
End If
Next
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[houde]

Hey, thank you very much, this helps a lot!!!