Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

**DocAElstein** · 03-25-2020, 05:58 PM

http://www.excelfox.com/forum/showth...ll=1#post12897
Test blog

Loop backwards when deleting rows

Important notes in support of this post: http://www.excelfox.com/forum/showth...ll=1#post12897

When deleting rows, ( and when deleting things generally ) , in a Loop, we will usually need to loop backwards.
If we loop backwards, things “behind us” were already considered, and so no strange effects will be noticed if they are effected by further deletions:
If we Loop forwards , rows will shift up after a delete, and so when moving on a row we may miss a row that is needed to be deleted, or other strange effects may occur:
Due to the deletion, things “ahead of us” , which we have not yet considered, may change in some way. The row number or item number, etc., of something not yet considered may change: This can cause VBA to get confused. We may get the wrong results, or worse, cause some coding error:
At the start of a loop, the parameters such as start, stop, and increment are set. Changing these after the loop begins may cause problems. It is generally bad practice to change loop parameters after the loop begins and before the loop ends, especially if those parameters are to be further used before the loop ends.

For example, in the case of deleting things in a looping process, this may sometimes give us problems:

__For Cnt = 1 To 4 Step 1 ' __ 1 2 3 4

Usually, this alternative, would overcome problems:

__For Cnt = 4 To 1 Step -1 ' __ 4 3 2 1

Example

We want to delete rows based on value in column C, in the range A1:C4: If the value is Delete this row , then the entire row should be deleted

Before:-

_____ Workbook: Delete Rows.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F
1	11	a	Do not delete
2	12	B	Delete this row
3	13	c	Delete this row
4	14	D	Do not delete
5
6				Original Range:-
7				11	a	Do not delete
8				12	B	Delete this row
9				13	c	Delete this row
10				14	D	Do not delete
11

Worksheet: MySheet

So in the above example, we want to delete rows 2 and 3.

We could try this macro, but it gives the wrong results. At first glance we would expect it to work.
It loops through the rows, and deletes the row if the value in column C is Delete this row. One could be forgiven for thinking that it should work.

Code:

Option Explicit
Sub LoopForwardsToDeleteRows()
Rem 1 Worksheets info
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = ThisWorkbook: Set Ws = Wb.Worksheets.Item(1)

Rem 2 Loop to delete rows
Dim Rws As Long
    For Rws = 1 To 4 '   1 2 3 4
        If Ws.Range("C" & Rws & "").Value = "Delete this row" Then
         Ws.Range("C" & Rws & "").EntireRow.Delete Shift:=xlUp ' Delete entire row, and  Shift  all rows above up to fill space
        Else
        ' Do nothing
        End If
    Next Rws
End Sub

After running the above macro we have

_____ Workbook: Delete Rows.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F
1	11	a	Do not delete
2	13	c	Delete this row
3	14	D	Do not delete
4
5				Original Range:-
6				11	a	Do not delete
7				12	B	Delete this row
8				13	c	Delete this row
9				14	D	Do not delete
10
11

Worksheet: MySheet

This is what goes on:
Nothing is done to the first row. No problems
The second row is deleted as expected, because cell C2 value was Delete this row
After the second row is deleted, the rows which were after the second row, are all shifted one row up so as to fill the space or “hole” left by the removed row. ( We cannot have a “black hole” in an Excel worksheet:. Excel does not allow this. – The spreadsheet cells are moved so as to “fill” the hole made by the deletion. “New” cells are added as necessary at the worksheet perimeter – In this case a new virgin row is added at the bottom of the worksheet )
The result of the second row being deleted, and the necessary shifting of cells to fill the “hole” which is done, is as follows:
Our original 4th row now becomes the 3rd row. That does not cause any problems.
Our original 3rd row now becomes the 2nd row. This is the problem. The second row has already been considered. It will not be considered again. The original 3rd row, ( now, as a result of the first deletion and cell shifting, the second row ) will not be considered. So it remains. It is not considered. It will therefore not be deleted.
When looping forward and deleting, rows not yet considered will be moved: This may cause problems.

The solution to the problem is to loop backwards. When looping backwards, if a row is deleted, then all rows “behind”/ “above” are shifted down. All those rows have already been considered, and either left as they are or deleted.
The next row to be considered, when looping backwards in a worksheet, will always be the next, not yet considered, row, regardless of whether the last row considered was deleted or not: None of the rows not yet considered have been shifted.
When looping backwards and deleting, rows not yet considered will not have been moved

So we try again

Before, ( as in previous example )

_____ Workbook: Delete Rows.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F
1	11	a	Do not delete
2	12	B	Delete this row
3	13	c	Delete this row
4	14	D	Do not delete
5
6				Original Range:-
7				11	a	Do not delete
8				12	B	Delete this row
9				13	c	Delete this row
10				14	D	Do not delete
11

Worksheet: MySheet

Macro: ( looping backwards )

Code:

Sub LoopBackwardsToDeleteRows()
Rem 1 Worksheets info
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = ThisWorkbook: Set Ws = Wb.Worksheets.Item(1)

Rem 2 Loop to delete rows
Dim Rws As Long
    For Rws = 4 To 1 Step -1  '   4 3 2 1
        If Ws.Range("C" & Rws & "").Value = "Delete this row" Then
         Ws.Range("C" & Rws & "").EntireRow.Delete Shift:=xlUp ' Delete entire row, and  Shift  all rows above up to fill space
        Else
        ' Do nothing
        End If
    Next Rws
End Sub