export visible rows only

[Cooz]

Hi everyone,

I use this code to copy a worksheet from one workbook to another:

With wbkExport
wbkCurrent.Worksheets("Output").Copy After:=.Worksheets(1)
End With

The problem is that this worksheet contains hidden rows that should not be
copied. Post-editing with

For Each aRow In wbkExport.Worksheets("Output").UsedRange.Rows
If aRow.Hidden Then
aRow.Delete
End If
Next

does not seem to work - and if it did, it would slow down the macro
considerably (but that is better than getting the hidden rows in the export).
So: I am looking for a fast way to export only visible rows to a new
worksheet. What should I do?

Thank you,
Cooz

 

[Gary''s Student]

Use this loop for the delete:

Sub cooz()
Worksheets("Output").Activate
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
For n = nLastRow To 1 Step -1
If Cells(n, "A").EntireRow.Hidden = True Then
Rows(n).Delete
End If
Next
End Sub

 

[Stefi]

Try this code piece (after unwanted cells are hidden):

Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Destination:=Sheets("Output").Range("A1").Select


Regards,
Stefi

„Cooz†ezt írta:

 

[Stefi]

Oops, this is the correct code:
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy Destination:=Sheets("Sheet2").Range("A1")
Stefi


„Stefi†ezt írta:

 

[Cooz]

Thank you Gary"s Student.
This works nicely. However I found Stefi's proposal somewhat more elegant.
I appreciate your contribution.

Kind regards,
Cooz