Copy only hidden rows in a worksheet [SOLVED]

[netbeui]

There is a lot of posts involving the exclusion of hidden cells in copying data, but none I have found that targets only hidden cells.


Problem statement:

I need to copy only the hidden Rows in one worksheet to another new worksheet in the same workbook.


Thank you,

Netbeui

 

[Debraj]

Hi Netbeui!


WOW.. Nice name & Interesting Question..


Check this..

[pre]

-------
Sub TestIt()
AntiRange(Sheets("Sheet1").UsedRange, Sheets("Sheet1").UsedRange.SpecialCells(12)).Copy
End Sub

Function AntiRange(BigRg As Range, ExcludeRg As Range) As Range
Dim NewRg As Range, CurrCell As Range
For Each CurrCell In BigRg.Cells
If Intersect(CurrCell, ExcludeRg) Is Nothing Then
If NewRg Is Nothing Then
Set NewRg = CurrCell
Else
Set NewRg = Union(NewRg, CurrCell)
End If
End If
Next
Set AntiRange = NewRg
End Function

[/pre]

Regards,

Deb

 

[netbeui]

Deb, I ran the code, but nothing seems to happen after execution; what should I look for? am I missing something? I expected it to create a new workseet, and the hidden information to appear there.


Thank you for your help,

Netbeui

 

[netbeui]

Deb:

I read a little deeper into the code and realized all I had to do was paste into the new sheet manually, my apologies.


Really quite brilliant, thank you!!

 

[Debraj]

Hi Netbeui!


Download this working file..


https://dl.dropboxusercontent.com/u/78831150/Excel/Copy%20only%20hidden%20rows%20in%20a%20worksheet.xlsb


Please let us know, if you have any query..


Regards,

Deb

 

[netbeui]

Thank you so much, very very nicely done,


-Netbeui

 

[Debraj]

HI Netbeui!


Thanks for the complement.. and glad that you solved it..


Regards!

Deb