Copy visible cells

paul20091968 · December 20th, 2006, 05:23 AM

Hi all,

I would like to copt the visible cells on a worksheet but NOT record number 1 how can this be achieved.
I have this:

ThisWorkbook.Sheets(4).UsedRange.SpecialCells(xlCe llTypeVisible).Select

But how can I change this to exclude record number 1

Regards,

Paul.

UnderTheBridge · December 22nd, 2006, 11:54 AM

I don't claim to be a wiz at VBA, but give this a try. I stepped through it and it seemed to work without doing anything to the selected visible cells.

Code:

Sub cpyfour()
    ThisWorkbook.Sheets(4).Rows("1:1").Select
    Selection.EntireRow.Hidden = True
    ThisWorkbook.Sheets(4).UsedRange.SpecialCells(xlCellTypeVisible).Select

    ' copy paste code here

    Rows("1:1").Select
    Selection.EntireRow.Hidden = False
End Sub

You might have to specify the original sheet(4) when setting the hidden property to false. As they say, your mileage may vary.

ltpeter · January 3rd, 2007, 11:43 PM

Sub SelectVisibleCellExceptFieldNameAndFirstRecord()
Dim tbl As Range
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 2, _
    tbl.Columns.Count).SpecialCells(xlCellTypeVisible) .Copy
    Sheets.Add After:=S heets(Sheets.Count)
    ActiveSheet.Paste
End Sub

N. Yauvasuta