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
Bookmarks