Loop based on cell value

gerald24

Board Regular
Joined
Apr 28, 2017
Messages
95
Hi,

Can anyone look into this? It is not working.
Basically, what I need is to delete every "Test" in a specific range that may change based on Range("A1").
If my understanding is correct, I wanted to set it up in Row 2 up to the last row, and any column based on A1 Value.

HTML:
Sub TEST()

    Dim cl As Range
    Dim Cell As Range
    Dim RngA As Range
    Dim RngB As Range
    
  LastRow = findLastRow(Sheets("Sheet1"))
  Set RngA = Cell(2, Range("A1").Value)
  Set RngB = Cell(2, LastRow)

            For Each cl In Range(RngA, RngB)
                If cl.Value = "Test" Then
                cl.EntireRow.Delete
                Else
                End If
            Next cl

End Sub

Set Function Last Row
HTML:
Function findLastRow(sheet As Worksheet) As Integer
    Set r = sheet.Cells.Find("*", sheet.Range("A1"), xlFormulas, , xlByRows, xlPrevious)
    If Not r Is Nothing Then
        findLastRow = r.Row
    Else
        findLastRow = 0
    End If
End Function


Thanks in advance!!!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi gerald24,

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Code:
Option Explicit
Sub TEST()
    
    Dim LastRow As Long
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
        
    LastRow = findLastRow(Sheets("Sheet1"))
    
    If LastRow > 2 Then
        'When looping to delete rows, need to work backwards through the rows
        For lngMyRow = LastRow To 2 Step -1
            If Range(Range("A1") & lngMyRow) = "Test" Then
                Rows(lngMyRow).EntireRow.Delete
            End If
        Next lngMyRow
    Else
        MsgBox "There were no rows past Row 2 to work with.", vbExclamation
    End If
    
    Application.ScreenUpdating = True

End Sub
Function findLastRow(sheet As Worksheet) As Long
    
    Dim r As Range

    Set r = sheet.Cells.Find("*", sheet.Range("A1"), xlFormulas, , xlByRows, xlPrevious)
    If Not r Is Nothing Then
        findLastRow = r.Row
    Else
        findLastRow = 0
    End If
    
End Function

Note the way the code was originally written it will try and delete the applicable rows from the current (active) sheet.

Regards,

Robert
 
Last edited:
Upvote 0
You haven't stated exactly what is in A1, but assuming it is a column number this code should work.
Note I am using a variant array instead of ranges because it is much faster, it is also best to start deleting the rows from the bottom because otherwise the row number to be deleted will change, which would mean reloading the variant array.
Code:
Sub TEST()

    
  lastrow = findLastRow(Sheets("Sheet1"))
  colno = Range("A1").Value
  inarr = Range(Cells(1, colno), Cells(lastrow, colno))
  For i = UBound(inarr) To 2 Step -1
        If inarr(i, 1) = "Test" Then
         Rows(i).EntireRow.Delete
        End If
 Next i




End Sub
 
Last edited:
Upvote 0
Hi gerald24,

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Code:
Option Explicit
Sub TEST()
    
    Dim LastRow As Long
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
        
    LastRow = findLastRow(Sheets("Sheet1"))
    
    If LastRow > 2 Then
        'When looping to delete rows, need to work backwards through the rows
        For lngMyRow = LastRow To 2 Step -1
            If Range(Range("A1") & lngMyRow) = "Test" Then
                Rows(lngMyRow).EntireRow.Delete
            End If
        Next lngMyRow
    Else
        MsgBox "There were no rows past Row 2 to work with.", vbExclamation
    End If
    
    Application.ScreenUpdating = True

End Sub
Function findLastRow(sheet As Worksheet) As Long
    
    Dim r As Range

    Set r = sheet.Cells.Find("*", sheet.Range("A1"), xlFormulas, , xlByRows, xlPrevious)
    If Not r Is Nothing Then
        findLastRow = r.Row
    Else
        findLastRow = 0
    End If
    
End Function

Note the way the code was originally written it will try and delete the applicable rows from the current (active) sheet.

Regards,

Robert

Thanks, Robert. Ill try this!
 
Upvote 0
You haven't stated exactly what is in A1, but assuming it is a column number this code should work.
Note I am using a variant array instead of ranges because it is much faster, it is also best to start deleting the rows from the bottom because otherwise the row number to be deleted will change, which would mean reloading the variant array.
Code:
Sub TEST()

    
  lastrow = findLastRow(Sheets("Sheet1"))
  colno = Range("A1").Value
  inarr = Range(Cells(1, colno), Cells(lastrow, colno))
  For i = UBound(inarr) To 2 Step -1
        If inarr(i, 1) = "Test" Then
         Rows(i).EntireRow.Delete
        End If
 Next i




End Sub

Hi Offthelip,

Could you explain to me the functions you used? Tried googling it but i cant understand. Just wanted to understand it so that next time I can do it on my own.
 
Upvote 0
Code:
Sub test()
lastrow = findLastRow(Sheets("Sheet1")) 'this is your function for finding the last row
 
  colno = Range("A1").Value 'i have assigned that value in A1 into a variant variable called colno (short for column number)
  ' Vba defaults to assigning the type variable if you don't declare the variable
  inarr = Range(Cells(1, colno), Cells(lastrow, colno)) ' I have assigned the values in the range between row 1 in the column given by colno
  'and the row number in lastrow and column colno into a variant array called inarr ( short for input array)
   ' once again I haven't declared the array, it isn't necessary but lots of people recommend it
   'This array is an array with two dimensions the first dimension goes from 1 to lastrow and the other dimension is 1
   ' this array holds exactly the same data as the data in the column as specified in colno and thus in A1
   ' Because it is held in memory it much faster to access that accessing the worksheet, so it will make looping through the rows 1000 times faster
  For i = UBound(inarr) To 2 Step -1 ' this loop thorugh the "rows" actually it loops through the data in inarr which is identical because we just copied it
        If inarr(i, 1) = "Test" Then ' this test the data in column colno, actually it is testing the data in inarr but it is the same
         Rows(i).EntireRow.Delete ' obvious!!
        End If
 Next i
End Sub
 
Last edited:
Upvote 0
Code:
Sub test()
lastrow = findLastRow(Sheets("Sheet1")) 'this is your function for finding the last row
 
  colno = Range("A1").Value 'i have assigned that value in A1 into a variant variable called colno (short for column number)
  ' Vba defaults to assigning the type variable if you don't declare the variable
  inarr = Range(Cells(1, colno), Cells(lastrow, colno)) ' I have assigned the values in the range between row 1 in the column given by colno
  'and the row number in lastrow and column colno into a variant array called inarr ( short for input array)
   ' once again I haven't declared the array, it isn't necessary but lots of people recommend it
   'This array is an array with two dimensions the first dimension goes from 1 to lastrow and the other dimension is 1
   ' this array holds exactly the same data as the data in the column as specified in colno and thus in A1
   ' Because it is held in memory it much faster to access that accessing the worksheet, so it will make looping through the rows 1000 times faster
  For i = UBound(inarr) To 2 Step -1 ' this loop thorugh the "rows" actually it loops through the data in inarr which is identical because we just copied it
        If inarr(i, 1) = "Test" Then ' this test the data in column colno, actually it is testing the data in inarr but it is the same
         Rows(i).EntireRow.Delete ' obvious!!
        End If
 Next i
End Sub

Understood the others.

Just these 2 lines? Why "To 2 Step - 1"? Sorry, I couldn't get this.
Code:
 For i = UBound(inarr) To 2 Step -1 ' this loop thorugh the "rows" actually it loops through the data in inarr which is identical because we just copied it
        If inarr(i, 1) = "Test" Then ' this test the data in column colno, actually it is testing the data in inarr but it is the same
 
Upvote 0
Just these 2 lines? Why "To 2 Step - 1"? Sorry, I couldn't get this.

When using a loop to delete rows one at a time like we're doing here you need to work backwards through the rows (see my comment on my suggested solution).

Regards,

Robert
 
Upvote 0
When using a loop to delete rows one at a time like we're doing here you need to work backwards through the rows (see my comment on my suggested solution).

Regards,

Robert

Then 2 stands for the Row (last row)? and -1 means it will go upwards?
 
Upvote 0
No, 2 is the row number to go to. Step -1 just means from the last row number down to 2.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top