Best approach for 'Cleaning' Multiple Excel Files

[EmmaP]

Hi everyone,

I am having a lot of issues with the best method to use for 'cleaning up' my data files in Excel.

I currently have 20 files with 10 sheets each to process.

I am trying to ---- Take an original sheet and copy it over to another sheet - which will include the 'cleaned' version of the data. The excel file that looks like the following below, but with many more applications
[TABLE="width: 500"]
<tbody>[TR]
[TD]Application Type[/TD]
[TD]Application Decision[/TD]
[TD]Total[/TD]
[TD]Title[/TD]
[TD]Address[/TD]
[TD]Ref Number[/TD]
[/TR]
[TR]
[TD]All[/TD]
[TD]All[/TD]
[TD]245[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Appeal Allowed[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Approved[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Deemed Permission[/TD]
[TD]1 [/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Deemed Refused[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Granted[/TD]
[TD]211[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]Conservation Area[/TD]
[TD]All[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The goal of this part is to copy the data over but eliminate the rows with a Decision value of zero. There are unfortunately multiple application types, some of which will have a zero value, but I need to keep these in. So the cleaned version of the table would look something like below,
[TABLE="width: 500"]
<tbody>[TR]
[TD]Application Type[/TD]
[TD]Application Decision [/TD]
[TD]Total[/TD]
[TD]Title [/TD]
[TD]Address[/TD]
[TD]Ref Number[/TD]
[/TR]
[TR]
[TD]All[/TD]
[TD]All[/TD]
[TD]245 [/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Deemed Permission[/TD]
[TD]1[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Granted[/TD]
[TD]211[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]Conservation Area[/TD]
[TD]All[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If you have any idea on how best to automate this I would apperciate this!

 

[Tim_Excel_]

It really depends on which rows you'd like to keep. What are the criteria for rows with Decision values of zero you'd like to keep?

 

[EmmaP]

I would like to keep All of the rows which have a label under the 'Application Type' even if their 'Application Decision' is a zero, but would like to get rid of the rows which just have an 'Application Decision' .

 

[Tim_Excel_]

Sub row_delete()

With Workbooks("[B]NAME[/B]").Worksheets([B]NUMBER[/B])
    'find last row
    LastRow = .Range("C" & Rows.Count).End(xlUp).Row
    x = LastRow
    For Each Cell In .Range("C1:C" & x)
       If Cell.Value = "0" And Cell.Offset(0, -2) = "" Then
            Cell.EntireRow.Delete
       End If
   Next
End With

End Sub