Macro that searches a column for all the blank cells then highlights the row entire row.

Hi all,

As stated above I am attempting to create a macro that will search a specific column for all the blank cells and highlight/selct the entire row, then copy the highlighted/slected rows to a new spread sheet.

So far I can search and highlight all blank cells within a range however when I add to the macro to allow it to highlight the entire row it only selects the first blank cell rather than all of them.

Any and all inputs would be greatly appreciated and any suggestion to improve the macro further would also be welcome.

Thanks,

Sub mySub()
Dim Rng As Range

ActiveSheet.Range("A1:A10").SpecialCells _
        (xlCellTypeBlanks).Select
ActiveCell.EntireRow.Select

    
    
    Set Rng = Selection
    Selection.Copy
   
    Sheets("Sheet2").Range(Rng.Address).PasteSpecial (xlPasteAll)
    

End Sub