Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop recheck
I have a for each loop that loops through a range of cells. My problem
is that if my criteria is met I delete the current row. The problem is that the for each loop moves on to the next item which is really 1 more down now that I deleted a row. How can I make it recheck the current row? I basically want it to back up 1 and then let the for each move ahead 1 to get me to the same line (which is the new one because of the deleted row). Here is the code I have. Sub MoveCompleted() Dim LastRow For Each cell In Sheets("Master Project List").Range("I7:I500") Debug.Print cell.Value If cell < "" Then Row = cell.Row Sheets("Completed Projects").UsedRange '<<-- Reset Used Range!! LastRow = Sheets("Completed Projects").Cells.SpecialCells(xlCellTypeLastCell). Row + 1 Sheets("Master Project List").Range(Row & ":" & Row).Cut Sheets("Completed Projects").Range(LastRow & ":" & LastRow) Sheets("Master Project List").Range(Row & ":" & Row).Delete End If Next cell End Sub Thanks Scott |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop recheck
Hi Scott,
Try: '============= Sub MoveCompleted() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim i As Long Dim CalcMode As Long Set WB = ThisWorkbook Set SH = WB.Sheets("Master Project List") Set Rng = SH.Range("I7:I500") On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For i = Rng.Cells.Count To 1 Step -1 With Rng.Cells(i) Debug.Print .Value If .Value < "" Then .EntireRow.Delete End If End With Next i XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<============= --- Regards, Norman "Riddler" wrote in message ups.com... I have a for each loop that loops through a range of cells. My problem is that if my criteria is met I delete the current row. The problem is that the for each loop moves on to the next item which is really 1 more down now that I deleted a row. How can I make it recheck the current row? I basically want it to back up 1 and then let the for each move ahead 1 to get me to the same line (which is the new one because of the deleted row). Here is the code I have. Sub MoveCompleted() Dim LastRow For Each cell In Sheets("Master Project List").Range("I7:I500") Debug.Print cell.Value If cell < "" Then Row = cell.Row Sheets("Completed Projects").UsedRange '<<-- Reset Used Range!! LastRow = Sheets("Completed Projects").Cells.SpecialCells(xlCellTypeLastCell). Row + 1 Sheets("Master Project List").Range(Row & ":" & Row).Cut Sheets("Completed Projects").Range(LastRow & ":" & LastRow) Sheets("Master Project List").Range(Row & ":" & Row).Delete End If Next cell End Sub Thanks Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop recheck
Here is what I came up with. It is crude but works good. When it
deletes a row it jumps out of the loop and starts over again. My list will never be very long so it wont have any performance issues. Private Sub CommandButton1_Click() Dim LastRow 10 For Each cell In Sheets("Master Project List").Range("I7:I500") If cell < "" Then Row = cell.Row Sheets("Completed Projects").UsedRange '<<-- Reset Used Range!! LastRow = Sheets("Completed Projects").Cells.SpecialCells(xlCellTypeLastCell). Row + 1 Sheets("Master Project List").Range("A" & Row & ":" & "P" & Row).Cut Sheets("Completed Projects").Range(LastRow & ":" & LastRow) Sheets("Master Project List").Range("A" & Row & ":" & "P" & Row).Delete GoTo 10 'Restart the loop because a row was deleted End If Next cell With Sheets("Completed Projects").Range("A4:P40000") .Sort Key1:=Sheets("Completed Projects").Range("I3"), Order1:=xlDescending End With End Sub Scott |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop recheck
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop recheck
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop recheck
Obviously
-- Don Guillett SalesAid Software "Riddler" wrote in message ps.com... Don Guillett wrote: Why not learn to do it properly? -- Don Guillett SalesAid Software Wow what a concept !!!!!!!!!!!!!!!!! I normally do what you mention but I am trying to learn better code practices than hacker coding, of which I know a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
always recheck data connection library in MOSS for latest data sou | Excel Discussion (Misc queries) | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |