Paste All but Hidden Data?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sefus12 - 23 Mar 2006 21:56 GMT

I've been trying to write a macro to copy and paste data from a column
in one spreadsheet over to a column in another spreadsheet. The
problem is that the first spreadsheet has several hidden rows of data
that I do not need in the second spreadsheet scattered within. Is
there a way to copy and paste just the data that is not hidden? Any
help would be appreciated. Thanks.

Signature

sefus12

Reply to this Message

N10 - 23 Mar 2006 22:07 GMT

Sub copyvisiblecells ()

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

end sub

This should get you started, substitue your own ranges and sheet names.

The key line of code is

Selection.SpecialCells(xlCellTypeVisible).Select

Best N10

"sefus12"

> I've been trying to write a macro to copy and paste data from a column
> in one spreadsheet over to a column in another spreadsheet. The
> problem is that the first spreadsheet has several hidden rows of data
> that I do not need in the second spreadsheet scattered within. Is
> there a way to copy and paste just the data that is not hidden? Any
> help would be appreciated. Thanks.

Reply to this Message

David McRitchie - 24 Mar 2006 18:25 GMT

Hi ....,

To eliminate hidden rows when copying, you can use the keyword shortcut
Ctrl+A to select all cells (slam the keys again if using Excel 2003)
Alt+; (semi-colon) to select visible cells
Ctrl+C to copy the selection
select new sheet
Ctrl+V to paste the selection (without the hidden rows)

If you still need a macro, see what happens if you do the above while recording
a macro.

BTW,
If you used a Filter you would only be copying the rows that are unfiltered.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Reply to this Message