Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change the FOR loop top
Hi,
I have a For loop that delets some rows from a sheet . The start value for the loop is 1 and the end value is the record count which is the number of rows in the sheet. When I delete a row I have less rows in the sheet so I decreese the record count by one and I want that the loop will loop just as the new value of the record count not its first value. For i=1 to recordCount ... objWS.rows(i).Delete recordCount=recordCount-1 next Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change the FOR loop top
The best approach is to loop from the bottom to the top.
For xR = LastRow to 1 step -1 ' your code Next xR To determine the Last Row use..... Dim LastRow as Long LastRow = Cells(Rows.Count,"A").End(xlup).Row This will determine the last row in Column A, change his as required. You can also use the column Number eg LastRow = Cells(Rows.Count,1).End(xlup).Row -- Cheers Nigel "DavidE" wrote in message ... Hi, I have a For loop that delets some rows from a sheet . The start value for the loop is 1 and the end value is the record count which is the number of rows in the sheet. When I delete a row I have less rows in the sheet so I decreese the record count by one and I want that the loop will loop just as the new value of the record count not its first value. For i=1 to recordCount ... objWS.rows(i).Delete recordCount=recordCount-1 next Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change the FOR loop top
If you start from the bottom and work up, you're life will be much easier:
for i = recordcount to -1 step -1 if something is true or false then rows(i).delete end if next i DavidE wrote: Hi, I have a For loop that delets some rows from a sheet . The start value for the loop is 1 and the end value is the record count which is the number of rows in the sheet. When I delete a row I have less rows in the sheet so I decreese the record count by one and I want that the loop will loop just as the new value of the record count not its first value. For i=1 to recordCount ... objWS.rows(i).Delete recordCount=recordCount-1 next Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change the FOR loop top
Thanks you Dave and Nigel. Now it's works perfect
"Dave Peterson" wrote: If you start from the bottom and work up, you're life will be much easier: for i = recordcount to -1 step -1 if something is true or false then rows(i).delete end if next i DavidE wrote: Hi, I have a For loop that delets some rows from a sheet . The start value for the loop is 1 and the end value is the record count which is the number of rows in the sheet. When I delete a row I have less rows in the sheet so I decreese the record count by one and I want that the loop will loop just as the new value of the record count not its first value. For i=1 to recordCount ... objWS.rows(i).Delete recordCount=recordCount-1 next Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change increasment in a for loop | Excel Discussion (Misc queries) | |||
Change while loop from count to formula | Excel Programming | |||
Change while loop from count to formula | Excel Programming | |||
Loop to change cell color based on found value? | Excel Programming | |||
Jump out of Do Loop on sheet change | Excel Programming |