Results 1 to 2 of 2

Loop to delete rows meeting criteria

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-13-2009
    Location
    Lincoln, IL
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    237

    Loop to delete rows meeting criteria

    This code sorts the result by Column A which has the values from d11 on the other sheets. It puts all the rows with no data in that column at the end. I want to delete all the rows that are blank in Column A. I assume I need a loop for that, but am not sure how to do it.

    Application.ScreenUpdating = False
    
    Application.DisplayAlerts = False
    Sheets("Refunds Due").Activate
        Dim WS As Worksheet
        Dim sht As Worksheet
        Dim strtRow As Long
        Dim rng As Range
        
        strtRow = 3 'set it to the row where you want to start it from
        
        Set sht = Sheets("Refunds Due")
        Set rng = sht.Range("A1")
         
         'clearing contents here :
         
         sht.Cells.ClearContents
         
         For Each WS In Worksheets
            If WS.Visible = xlSheetVisible And WS.Tab.ColorIndex <> 1 Then
         
                If WS.Name <> "Resident List by Unit" And WS.Name <> "Refunds Due" Then
                    WS.Range("d11").Copy
                    rng.Offset(strtRow, 0).PasteSpecial xlPasteValues
                    
                    WS.Range("b3").Copy
                    rng.Offset(strtRow, 1).PasteSpecial xlPasteValues
                    
                    WS.Range("b6").Copy
                    rng.Offset(strtRow, 2).PasteSpecial xlPasteValues
                    
                    WS.Range("d14").Copy
                    rng.Offset(strtRow, 3).PasteSpecial xlPasteValues
                    
                    strtRow = strtRow + 1
                End If
            End If
         Next WS
         
        Range("D4").Select
        Selection.ClearContents
        Range("A4").Select
        ActiveCell.FormulaR1C1 = "CONTRACT ENDED"
        Range("B4").Select
        ActiveCell.FormulaR1C1 = "RESIDENT"
        Range("C4").Select
        ActiveCell.FormulaR1C1 = "UNIT"
        Range("D4").Select
        ActiveCell.FormulaR1C1 = "AMOUNT DUE"
        Columns("A:D").Select
        Columns("A:D").EntireColumn.AutoFit
        Rows("1:3").Select
        Selection.Delete Shift:=xlUp
        Cells.Select
        Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
            , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
            , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
            xlSortNormal
        Range("A1").Select
        Range("A1:D1").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        
        Application.ScreenUpdating = True
    
    End Sub
    Last edited by narrowgate88; 05-18-2009 at 04:55 PM. Reason: solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1