CODEWITHSUNDEEP
vbaexcel
MTBthePRO
MTBthePRO

Reputation: 520

VBA: How to delete rows and keep some rows based on criteria?

I have been writing this macro which has three steps to it. First is to delete rows if rows are blank after Column C and the Second step is there are rows with titles that have to remain in the workbook like Contributions-All Other or Program Fees-Youth. The Third step is to format the row by adding spaces or empty rows after certain titles.

This my code and it doesn't seem to compile and I am not sure how prevent rows from deleting...please help. 

Sub RemoveRowsAndFormat()  
 Dim WS As Worksheet
 For Each WS In Sheets
 WS.Activate


   Dim n As Long
     Dim nlast As Long
     Dim rw As Range
     Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
     nlast = rw.Count
     For n = nlast To 9 Step -1
      If (rw.Cells(n, 3).Value = "Contributions-All Other" Or rw.Cells(n, 3).Value = "Program Fees - Youth" Or rw.Cells(n, 3).Value = "Financial Assitance" Or rw.Cells(n, 3).Value = "Salaries & Wages" Or rw.Cells(n, 3).Value = "Payroll Taxes" Or rw.Cells(n, 3).Value = "Employee Benefits" Or rw.Cells(n, 3).Value = "Staff Training and Confer." Or rw.Cells(n, 3).Value = "Occupancy" Or rw.Cells(n, 3).Value = "Supplies" Or rw.Cells(n, 3).Value = "Telephone" Or rw.Cells(n, 3).Value = "Postage & Shipping" Or rw.Cells(n, 3).Value = "Promotion and Advertising" Or rw.Cells(n, 3).Value = "Bad Debt" Or rw.Cells(n, 3).Value = "Program Operating Expense" Or rw.Cells(n, 3).Value = "Program Operating Net") Then
      rw.Rows(n).EntireRow.Insert
        ElseIf (rw.Cells(n, 4).Value = "" And rw.Cells(n, 5).Value = "" And rw.Cells(n, 6).Value = "" And rw.Cells(n, 7).Value = "" And rw.Cells(n, 8).Value = "" And rw.Cells(n, 9).Value = "" And rw.Cells(n, 10).Value = "" And rw.Cells(n, 11).Value = "") Then
             rw.Rows(n).Delete


         End If

     Next n
     Next WS
 End Sub

Upvotes: 0

Views: 118

Answers (1)

YowE3K
YowE3K

Reputation: 23994

The code in your (edited) question seems to be doing what you want, except that it is adding a row above your headings instead of below it. That could be fixed by changing rw.Rows(n).EntireRow.Insert to rw.Rows(n + 1).EntireRow.Insert but that could lead to problems (if a heading exists on the last row) due to the way you have defined rw.

I have refactored your code to use a Select Case statement to replace your (IMO) unwieldy If statement, and to refer to the worksheet rather than just certain rows when deciding where to perform insertions/deletions.

Sub RemoveRowsAndFormat()  
    Dim WS As Worksheet
    Dim n As Long
    Dim nlast As Long
    Dim rw As Range
    Dim c As Long
    Dim allEmpty As Boolean
    For Each WS In Worksheets
        With WS
            nlast = .UsedRange.Rows(.UsedRange.Rows.Count).Row
            For n = nlast To 9 Step -1
                Select Case .Cells(n, 3).Value 

                    Case "Contributions-All Other", _
                         "Program Fees - Youth", _
                         "Financial Assitance", _
                         "Salaries & Wages", _
                         "Payroll Taxes", _
                         "Employee Benefits", _
                         "Staff Training and Confer.", _
                         "Occupancy", _
                         "Supplies", _
                         "Telephone", _
                         "Postage & Shipping", _
                         "Promotion and Advertising", _
                         "Bad Debt", _
                         "Program Operating Expense", _
                         "Program Operating Net"

                        .Rows(n + 1).EntireRow.Insert

                    Case Else

                        allEmpty = True
                        For c = 4 To 11
                            If .Cells(n, c).Value <> "" Then
                                allEmpty = False
                                Exit For
                            End If
                        Next
                        'The above could be replaced by a "COUNTA", but I like this way
                        If allEmpty Then
                            .Rows(n).Delete
                        End If
                End Select
            Next n
        End With
    Next WS
End Sub

You say in a recent comment that a new issue "is NOT all of the titles need spacing". If so, the Select Case statement makes it easy to include that functionality as follows:

                Select Case .Cells(n, 3).Value 

                    'Do nothing for headings which we just want to leave alone
                    Case "Contributions-All Other", _
                         "Program Fees - Youth", _
                         "Financial Assitance", _
                         "Salaries & Wages", _
                         "Payroll Taxes", _
                         "Employee Benefits", _
                         "Staff Training and Confer.", _
                         "Occupancy", _
                         "Supplies"

                    'Process cases where an additional row needs to be inserted
                    Case "Telephone", _
                         "Postage & Shipping", _
                         "Promotion and Advertising", _
                         "Bad Debt", _
                         "Program Operating Expense", _
                         "Program Operating Net"

                        .Rows(n + 1).EntireRow.Insert

                    'For all the other rows, check whether it needs to be deleted
                    Case Else

                        allEmpty = True
                        '...

(Obviously, I have just made up which headings should have rows inserted after them and which shouldn't.)

That Select Case statement is just a simplified(?) way of writing the following If statement:

If .Cells(n, 3).Value = "Contributions-All Other" Or _
   .Cells(n, 3).Value = "Program Fees - Youth" Or _
   .Cells(n, 3).Value = "Financial Assitance" Or _
   .Cells(n, 3).Value = "Salaries & Wages" Or _
   .Cells(n, 3).Value = "Payroll Taxes" Or _
   .Cells(n, 3).Value = "Employee Benefits" Or _
   .Cells(n, 3).Value = "Staff Training and Confer." Or _
   .Cells(n, 3).Value = "Occupancy" Or _
   .Cells(n, 3).Value = "Supplies" Then

ElseIf .Cells(n, 3).Value = "Telephone" Or _
       .Cells(n, 3).Value = "Postage & Shipping" Or _
       .Cells(n, 3).Value = "Promotion and Advertising" Or _
       .Cells(n, 3).Value = "Bad Debt" Or _
       .Cells(n, 3).Value = "Program Operating Expense" Or _
       .Cells(n, 3).Value = "Program Operating Net" Then

    .Rows(n + 1).EntireRow.Insert

Else

    allEmpty = True
    '...
End If

P.S. Should "Financial Assitance" be "Financial Assistance"?

Upvotes: 1

Related Questions