Need A Shorter Code

[charllie]

Hi Folks,

Anyone out there have time to write me a short code for the following please.

I want to search column B in worksheet1 starting from row 6 downwards and for every cell that is > "" i want to copy them and place them in column B of worksheet2 starting from row 6 and continue downwards.

I know how to do it like this for each cell:

If Sheets1.Range ("B6").value >"" then
sheets2.Range ("B6").value = Sheets1.Range ("B6").value
End If

But not sure how to do it short code


Thanks

 

[Von Pookie]

Assuming the blank cells are truly blank (that is, not just displaying as blank due to a formula result), how about this?

Sub test()
Dim myRng As Range

With Sheets("Sheet1")
    Set myRng = .Range("B6", .Range("B65536").End(xlUp))
End With

With myRng
    .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("B6")
    .Rows.Hidden = False
End With

End Sub

 

[charllie]

Hi Kristy,

Thank you very much that has done just what is needed.

Thank you again