VBA to copy columns, rows, and highlight for certain criteria that is in a dynamic report

**kristymcgee** · 10-30-2012, 04:59 PM

Good afternoon,

I have a doozy. I am working with an admin that receives a very large report each month and spends an inordinate amount of time formatting the report to fit the specifications. I am new to coding, so I have been unable to even find a place to start. I have attached a test document with data. I want to make a template for her to use. I think she could just paste the report in the one tab and have the coding do the rest. Here are the rules:
1. Only needs columns c, e-l, n-q, s-v, and AC
2. Sort by column A (facility name)
3. Delete all rows for facilities (column A) Battle Creek, Health, Inc, (and two more)
4. Delete all rows for courses (column E) with the names "dummy" "testing" or "fake" included in the name
5. Delete all rows that have a create date (column I) prior to the month that you are pulling (dynamically changing)

There are several other objects, but I just need help getting started. Any assistance is greatly appreciated!

**mike7952** · 10-30-2012, 05:40 PM

This I think can get you started. Im not sure about Delete all rows that have a create date (column I) prior to the month that you are pulling (dynamically changing)
What determines the prior month? Also code dosent include Sorting in column A but that could be done after we get all the rows deleted

Sub abc()
 Dim ptr As Long, indx As Long
 Dim aFacilities, aCourses

 aFacilities = Array("Battle Creek", "Health", "Inc") 'Add two more here
 aCourses = Array("dummy", "testing", "fake")
 
 
 Application.ScreenUpdating = False
 For ptr = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
'   Delete all rows for facilities (column A) Battle Creek, Health, Inc,
    For indx = LBound(aFacilities) To UBound(aFacilities)
        If Cells(ptr, "a").Value = aFacilities(indx) Then
            Rows(ptr).Delete
            GoTo Nextptr
        End If
    Next
'   Delete all rows for courses (column E) with the names "dummy" "testing" or "fake" included in the name
    For indx = LBound(aCourses) To UBound(aCourses)
        If InStr(1, Cells(ptr, "e").Value, aCourses(indx), vbTextCompare) > 0 Then
            Rows(ptr).Delete
            GoTo Nextptr
        End If
    Next
Nextptr:
 Next
 Application.ScreenUpdating = True
End Sub