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

Application Type	Application Decision	Total	Title	Address	Ref Number
All	All	245	xxx	xxx	xxx
			xxx	xxx	xxx
	Appeal Allowed	0
	Approved	0
	Deemed Permission	1	xxx	xxx	xxx
	Deemed Refused	0
	Granted	211	xxx	xxx	xxx
			xxx	xxx	xxx
Conservation Area	All	0

<tbody>
</tbody>

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,

Application Type	Application Decision	Total	Title	Address	Ref Number
All	All	245	xxx	xxx	xxx
			xxx	xxx	xxx
	Deemed Permission	1	xxx	xxx	xxx
	Granted	211	xxx	xxx	xxx
Conservation Area	All	0

<tbody>
</tbody>

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