Option Explicit
Sub Test()
Dim Cell As Range
Sheets("Sheet2").UsedRange.ClearContents
With Sheets(1)
' loop column B untill last cell with value (not entire column)
For Each Cell In .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
If Cell.Value <> "" Then
' Copy>>Paste in 1-line (no need to use Select)
.Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)
End If
Next Cell
End With
blnkrowdel
Sheets("Sheet2").PrintOut
End Sub
Sub blnkrowdel()
Dim wsCH As Worksheet
Dim lRow As Variant
Set wsCH = Sheet2
With wsCH
lRow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("B1:B" & lRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
Sub ClearColBSht1()
Dim Cell As Range
Application.ScreenUpdating = False
With Sheets(1)
' loop column B untill last cell with value (not entire column)
For Each Cell In .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
Cell.Value = ""
Next Cell
End With
Application.ScreenUpdating = True
End Sub
There is more code in the SHEET1 module that creates the CHECK MARK in Col B, when a cell in Col B is double-clicked.
Bookmarks