 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008export visible rows only
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 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  SignatureGary''s Student - gsnu200788 > Hi everyone, > [quoted text clipped - 20 lines] > Thank you, > 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 > Use this loop for the delete: > [quoted text clipped - 33 lines] > > Thank you, > > Cooz 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: > Hi everyone, > [quoted text clipped - 20 lines] > Thank you, > Cooz Oops, this is the correct code: Selection.CurrentRegion.Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Destination:=Sheets("Sheet2").Range("A1") Stefi „Stefi” ezt írta: > Try this code piece (after unwanted cells are hidden): > [quoted text clipped - 31 lines] > > Thank you, > > Cooz |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.