copying an excel list

balllian · October 14th, 2004, 04:29 AM

i have a list of data which contains hidden rows when i go to copy the visble list over to another spreadsheet it unhides all the columns. is there a way of stopping this happen so that i can copy only the visible columns accross. :)

slgknjn · October 14th, 2004, 07:15 AM

Hi Ballian

Here is an example on how it can be done.

Sub CopyUnhidden()
    Dim J, TargetRow As Integer
    TargetRow = 1
    For J = 1 To 20
        If Not Sheet1.Rows(J).Hidden Then
            Sheet2.Cells(TargetRow, 1).Value = Sheet1.Cells(J, 1).Value
            TargetRow = TargetRow + 1
        End If
     Next J
End Sub

Cheers
Karsten

BrianB · October 15th, 2004, 02:54 AM

Something like this ... ?

Code:

Dim CopyRange As Range
Set CopyRange = ActiveSheet.Range("A1:F2000").SpecialCells(xlCellTypeVisible)
CopyRange.Copy
ThisWorkbook.Worksheets("Sheet1").Paste Destination:=Worksheets("Sheet1").Range("A1")

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.