delete rows based on rules.

[ra303]

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.

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

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

 

[Lewiy]

Welcome to the board!!

Does this do what you want?:

Dim Limit As Long, c As Long
Dim LastCol As Long
Limit = UsedRange.Rows.Count
For c = Limit To 1 Step -1
    LastCol = Cells(c, Columns.Count).End(xlToLeft).Column
    If Cells(c, LastCol) = 0 Or Len(Cells(c, 1)) > 3 Then
        Rows(c).Delete shift:=xlUp
    End If
Next c

 

[Peter_SSs]

ra303

Welcome to the MrExcel board!

If you happen to have a lot of data, a filter method may be quicker than a looping method.

It is a little unclear to me whether the "0 rows" to be deleted are those with 0 in the last column or those with 0 in the ActiceCell column. I have gone with your code which is indicating the ActiveCell column.

Anyway, you might like to give this a try to see if it does what you want.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Del_rows_with_zero_in_column_of_activecell()
    <SPAN style="color:#00007F">Dim</SPAN> StopRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.Calculation = xlCalculationManual
    Col = ActiveCell.Column
    <SPAN style="color:#00007F">Const</SPAN> StartRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 1 <SPAN style="color:#007F00">'Row to Start looking at</SPAN>
    
    Rows(1).EntireRow.Insert
    StopRow = Cells(Rows.Count, Col).End(xlUp).Row
    <SPAN style="color:#00007F">With</SPAN> Range(Cells(1, Col), Cells(StopRow, Col))
        .AutoFilter Field:=1, Criteria1:="=0"
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Err.Clear
        .AutoFilter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    StopRow = Cells(Rows.Count, 1).End(xlUp).Row
    <SPAN style="color:#00007F">With</SPAN> Range("A1:A" & StopRow)
        .AutoFilter Field:=1, Criteria1:="=????*"
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Err.Clear
        .AutoFilter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Rows(1).EntireRow.Delete
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[ra303]

thanx for the replies guys. the Spreadsheet in question is the daily stock market data from Float.com.au, http://www.float.com.au/scgi-bin/prod/dl.cgi . If you look at the spreadsheet it has heaps of data where the volume in the last column is 0. So I delete that row. Further I do not want stocks that have a ticker name of more than three characters so I need to delete that as well.

Lewiy: your code gave me a "Invalid outside procedure " error. What am I doing worng ?

Peter_SSs: your code worked, but I had to select the column. Can it parse the column A for the three letter criteria and column G for 0. Also is it possible for excel to read these CSV files from a folder and do it bulk to about 30 CSV files. Also please put comments in your code, it helps me learn and I wont have to bother you guys so much.

thanx for all the help, its really appreciated.[/img]

 

[Lewiy]

Sorry, you need to put it in a Sub, i.e:

Sub MyMacro()
Dim Limit As Long, c As Long 
Dim LastCol As Long 
Limit = UsedRange.Rows.Count 
For c = Limit To 1 Step -1 
    LastCol = Cells(c, Columns.Count).End(xlToLeft).Column 
    If Cells(c, LastCol) = 0 Or Len(Cells(c, 1)) > 3 Then 
        Rows(c).Delete shift:=xlUp 
    End If 
Next c
End Sub

 

[Peter_SSs]

Peter_SSs: your code worked, but I had to select the column. Can it parse the column A for the three letter criteria and column G for 0.

I did not look at the linked file, but if it is always columns A and G, to use my filter method, just change

Col = ActiveCell.Column

to

Col = 7

 

[ra303]

Thanx for that Peter.

 

[ra303]

I tried the code below to read excel files from a folder and then run Peter_SSs code, but somehow it failed. Can you guys help please. thanx a lot.

Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim StopRow As Long
Dim Col As Long
MyPath = "C:\test"
ChDir MyPath
TheFile = Dir("*.xls")
For Each wb In TheFile
Do While TheFile <> ""
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Col = 7
    Const StartRow As Long = 1 'Row to Start looking at
        Rows(1).EntireRow.Insert
    StopRow = Cells(Rows.Count, Col).End(xlUp).Row
    With Range(Cells(1, Col), Cells(StopRow, Col))
        .AutoFilter Field:=1, Criteria1:="=0"
        On Error Resume Next
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Err.Clear
        .AutoFilter
    End With
    StopRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("A1:A" & StopRow)
        .AutoFilter Field:=1, Criteria1:="=????*"
        On Error Resume Next
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Err.Clear
        .AutoFilter
    End With
    Rows(1).EntireRow.Delete
        Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Loop
End Sub

 

[Peter_SSs]

I tried the code below to read excel files from a folder and then run Peter_SSs code, but somehow it failed. Can you guys help please. thanx a lot.

I am not sure that I will be able to help anyway, but without knowing in what way it failed and/or any error messages and the offending code lines, it is a bit hard to start taking guesses.

 

[ra303]

Thanx for the reply Peter.

the first error I got was :
For each may only inerate over a collection object or an array.

so I commented out the line For Each wb In TheFile

then I got the error:
Run-time error '1004'
AutoFilter method of Range class failed