jmurray394
New Member
- Joined
- Mar 7, 2022
- Messages
- 6
- Office Version
- 2016
- Platform
- Windows
I'm trying to make the following code work. The purpose of it is to search through a sheet and if the completed column is "yes" it will then cut that row into the completed tab. After it cuts to the row, it then needs to go back to the original sheet and delete the leftover blank rows. I keep hitting snags only when it comes to deleting blank rows. The for statement will delete the majority of the blanks but there will still be some left over, causing me to have to run the macro twice.
VBA Code:
Option Explicit
Sub Markcomplete()
Dim wb As Workbook
Dim summary, b3, completed As Worksheet
Dim lr, clr, row As Long
Dim table As ListObject
Set wb = ThisWorkbook
Set summary = wb.Sheets("Summary")
Set b3 = wb.Sheets("B3")
Set completed = wb.Sheets("Completed")
Application.ScreenUpdating = False
lr = b3.Cells(b3.Rows.Count, 1).End(xlUp).row
With wb.Sheets("B3")
For row = 2 To lr 'the data in my table starts on A2
On Error Resume Next
If b3.Cells(row, 3) = "YES" Then 'if column c is yes then cut that row into completed tab
b3.Rows(row).Cut
completed.Activate
clr = completed.Cells(completed.Rows.Count, 1).End(xlUp).row
completed.Cells(clr + 1, 1).Select
ActiveSheet.Paste
End If
Next row
lr = b3.Cells(b3.Rows.Count, 1).End(xlUp).row
For row = 2 To lr
If b3.Cells(row, 2) = 0 Then
b3.Rows(row).EntireRow.Delete
End If
On Error Resume Next
Next row
End With
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
clr = completed.Cells(completed.Rows.Count, 1).End(xlUp).row
Set table = completed.ListObjects("Table4")
table.Resize table.Range.Resize(clr)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.ScreenUpdating = True
End Sub