Need A Shorter Code

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
974
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Assuming the blank cells are truly blank (that is, not just displaying as blank due to a formula result), how about this?

Code:
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

Well-known Member
Joined
Apr 6, 2005
Messages
974
Hi Kristy,

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

Thank you again :biggrin: :biggrin: :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top