Hi everyone, I am trying to clear row with the following rules:
the last cell must not contain 0, if 0 delete entire row.
the first cell the the A row should be only three character long, if more delete entire row.
Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .
the script is here, your helps is much appreciated.
here is the sample sheet.
ABP 20070821 1.78 1.795 1.77 1.795 996626
ABQ 20070821 0.42 0.42 0.415 0.415 175123
ABS 20070821 6.3 6.43 6.23 6.3 5702802
ABSIZQ 20070821 2.65 2.65 2.65 2.65 6000
ABSSMW 20070821 3.4 3.4 3.4 3.4 5000
ABSWMG 20070821 0.14 0.14 0.14 0.14 2000
ABU 20070821 0.14 0.14 0.13 0.14 1116777
the last cell must not contain 0, if 0 delete entire row.
the first cell the the A row should be only three character long, if more delete entire row.
Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .
the script is here, your helps is much appreciated.
Code:
Sub Del_rows_with_zero_in_column_of_activecell()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Dim cnt As Long
For cnt = StopRow To StartRow Step -1
If Not IsEmpty(Cells(cnt, Col)) Then
If IsNumeric(Cells(cnt, Col)) Then
If Cells(cnt, Col) = 0 Then Rows(cnt).Delete
End If
End If
Next cnt
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
ABP 20070821 1.78 1.795 1.77 1.795 996626
ABQ 20070821 0.42 0.42 0.415 0.415 175123
ABS 20070821 6.3 6.43 6.23 6.3 5702802
ABSIZQ 20070821 2.65 2.65 2.65 2.65 6000
ABSSMW 20070821 3.4 3.4 3.4 3.4 5000
ABSWMG 20070821 0.14 0.14 0.14 0.14 2000
ABU 20070821 0.14 0.14 0.13 0.14 1116777