selecting visible rows

Originally Posted by mikerickson

Do you mean visible as in "not hidden" or visible as in "within the current scroll area"?

If the former, you could use

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)

hi i mean within the current scroll area.
and i am sorry but i dont understand your reply as it is above my head , can you explain your solution a bit more simpler for me to understand please as i am a newbie to this

The VB code

ActiveWindow.VisibleRange.SpecialCells(xlCellTypeVisible)

will return the range of cells that is currently visible in the window. The .SpecialCells is to eliminate those rows that would be in the window, but have been hidden.
This macro

Sub SelectWindow()
    ActiveWindow.VisibleRange.SpecialCells(xlCellTypeVisible).Select
End Sub

will select that.
This macro will copy the values (equivilant to Copy/PasteSpecialValues) in that range to the same range on "sheet2"

Sub copyWindowValues()
    Dim xRay As Range
    Set xRay = ActiveWindow.VisibleRange.SpecialCells(xlCellTypeVisible)
    xRay.Parent.Parent.Sheets("sheet2").Range(xRay.Address).Value = xRay.Value
End Sub

This one copies formulas (equivilant to Copy/Paste)

Sub copyWindowToTwo()
    Dim xRay As Range
    Set xRay = ActiveWindow.VisibleRange.SpecialCells(xlCellTypeVisible)
    xRay.Parent.Parent.Sheets("sheet2").Range(xRay.Address).Formula = xRay.Formula
End Sub

i can see how the codes work but i just want it to select rows with data in and not if they contain blank rows

Add this line of code.

Set xray = Application.Union(xray.SpecialCells(xlCellTypeFormulas), xray.SpecialCells(xlCellTypeConstants))