selecting visible rows

hi all , i need to be able to select only visible rows and columns that contain data, eg if columns A,B,C,D,E up to row 26 for example, then i want to select this group, copy it and then transfer it all to sheet 2, (even if there are a couple of blanks in the selection i want to include them also) if more rows of data is included then they will be included as well, if this is possible then can it be used in a macro? and if so how do i go about it please?

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))