Delete rows based on multiple criterias, copy/paste data and formulas

**BQuek** · 06-24-2013, 10:10 AM

Excel 2010 - I need help to delete rows of a report which contains $- or zero value. The report is grouped by account number (in hundreds of account group and thousands of rows) and not in the list format, which make it difficult to use Excel filter and formula to delete the unwanted $- value.

1. I need to delete the $- rows but not "Beginning Balance with $-" should remain in the report. But if the "Subtotal" is $-, then need to delete all the rows from "Account Number" to "Subtotal" plus one empty row (Beginning Balance will be deleted too). This program should continue to search and delete until all $- rows are removed.

2. For each rows (data between "Beginning Balance" to the last row of that Account Number group), I need to copy the Account Number (example 211-00-5936-00-001-3-00-2-00 etc) and paste beside the Data in column F, paste formula =(IF(B11="","7/1/2012",EOMONTH(DATEVALUE(B11),0))) in column G, paste formula =MID(F11,8,4) in column H, and past formula =MID(F11,16,3) in column I.

I attached the file with raw data on ReportData sheet and the expected output on FinishedReport. Appreciate any assistance, help, or reference.

**JasperD** · 06-24-2013, 11:02 AM

Try this - I didn't actually put the formulas in the cells, just the results. Let me know if you actually want the formulas

Sub test()
Dim lr&, i&, x&, accnum$

lr = Range("E65536").End(xlUp).Row
For i = lr To 1 Step -1

If Cells(i, 5).Value = "0" Then

If Cells(i, 4).Value = "BEGINNING BALANCE" Then GoTo nxt
If Cells(i, 4).Value <> "" Then Cells(i, 4).EntireRow.Delete: GoTo nxt

If Cells(i, 2).Value = "Subtotal" Then
x = 1
Do Until Cells(i - x, 2).Value = "Account Number"
x = x + 1
Loop

Range(Cells(i - x - 1, 1), Cells(i, 1)).EntireRow.Delete
End If

nxt:
End If

Next i


For Each cell In Range("D1:D" & Range("D65536").End(xlUp).Row)
If cell.Value = "BEGINNING BALANCE" Then
accnum = cell.Offset(-4, -2).Value

x = 0
Do Until cell.Offset(x, 0).Value = ""
cell.Offset(x, 2).Value = accnum

cell.Offset(x, 3).NumberFormat = "dd/mm/yyyy"

If cell.Offset(x, -2) = "" Then
cell.Offset(x, 3).Value = "7/1/2012"
Else
cell.Offset(x, 3).Value = WorksheetFunction.EoMonth(cell.Offset(x, -2).Value, 0)
End If

cell.Offset(x, 4).NumberFormat = "@"
cell.Offset(x, 4).Value = Mid(accnum, 8, 4)
cell.Offset(x, 5).NumberFormat = "@"
cell.Offset(x, 5).Value = Mid(accnum, 16, 3)

x = x + 1

Loop

End If

Next

End Sub

Please click the * below if this helps