Problem IF statement

[smurray444]

Dear all,

I have the following VBA code (shown below) which activates a row delete loop if one or more conditions are met. However, I only want to activate the loop if for a given row in column F (column 6), the value is greater than 10000. The code as it stands is:

Option Explicit

Sub CellBuster()
   Dim lCount As Double
   Dim lRows As Double
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   lRows = ActiveSheet.UsedRange.Rows.Count
   For lCount = lRows To 1 Step -1
       If Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -1).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -2).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -3).Value Then
           Cells(lCount, 6).EntireRow.Delete
       End If
   Next
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
End Sub

This version appears to work.

My attempt to include the previously mentioned extension is as follows:

Option Explicit

Sub CellBuster()
   Dim lCount As Double
   Dim lRows As Double
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   lRows = ActiveSheet.UsedRange.Rows.Count
   If Cells(lRows, 6).Value > 10000 Then
   For lCount = lRows To 1 Step -1
       If Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -1).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -2).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -3).Value Then
           Cells(lCount, 6).EntireRow.Delete
       End If
   Next
   End If
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
End Sub

However, although I get no errors, the code appears to do nothing (i.e. doesn't delete any rows), even though there are cells of greater value than 10000 in column F.

Would someone be able to provide some advice as to what I'm doing wrong please?

Thanks
smurray444

[stevebriz]

I think your probelm is here:

lRows = ActiveSheet.UsedRange.Rows.Count

it should be:

lRows = ActiveSheet.UsedRange.Rows

[avveerkar]

Dear all,

I have the following VBA code (shown below) which activates a row delete loop if one or more conditions are met. However, I only want to activate the loop if for a given row in column F (column 6), the value is greater than 10000. The code as it stands is:

Option Explicit

Sub CellBuster()
   Dim lCount As Double
   Dim lRows As Double
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   lRows = ActiveSheet.UsedRange.Rows.Count
   For lCount = lRows To 1 Step -1
       If Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -1).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -2).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -3).Value Then
           Cells(lCount, 6).EntireRow.Delete
       End If
   Next
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
End Sub

This version appears to work.

My attempt to include the previously mentioned extension is as follows:

Option Explicit

Sub CellBuster()
   Dim lCount As Double
   Dim lRows As Double
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   lRows = ActiveSheet.UsedRange.Rows.Count
   If Cells(lRows, 6).Value > 10000 Then
   For lCount = lRows To 1 Step -1
       If Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -1).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -2).Value Then
           Cells(lCount, 6).EntireRow.Delete
       ElseIf Cells(lCount, 6).Value > 1.5 * Cells(lCount, 6).Offset(, -3).Value Then
           Cells(lCount, 6).EntireRow.Delete
       End If
   Next
   End If
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
End Sub

However, although I get no errors, the code appears to do nothing (i.e. doesn't delete any rows), even though there are cells of greater value than 10000 in column F.

Would someone be able to provide some advice as to what I'm doing wrong please?

Thanks
smurray444

There are two issues with your macro.
1. You perhaps want lRow to be the last row of the used range. But you are not really achieving this if your used range does not start from the first row. To explain, suppose the used range has 10 rows and starts from say row 6 ( ie from row 6 to row 15 ). lRows = ActiveSheet.UsedRange.Rows.Count will give lRow = 10 but your last row of the region is 15. To get absolute address of the last row lRow should really be Activesheet.Usedrange.Row+Activesheet.Usedrange.Rows.Count-1

2. The way you have written the macro, the loop executes only if the last row has more than 10000 in column F. If it is not then the loop does not get executed at all. The macro checks if in the last row, col F has more than 10000 and if it is true then deletes those rows which have more than 1.5 in F.
Is that what you want? If not and if you want to delete all the rows in the region with value more than 10000 in F then why don't you just replace 1.5 with 10000 in your first macro?

A V Veerkar