Range with hidden columns filtered question

[Lowell In the south]

I have a range I attempting to get a macro to paste too. A10:Z19 with in this range could be any of a couple of hidden columns. I am using bits of code I picked up from some posts on this board one from MREXCEL himself on Feb 9 2002.


Range("Ag2:Ah3").Copy
Dim Rg As Range
Dim cl As Range
Set Rg = Range("A10:z19").SpecialCells(xlCellTypeVisible)
For Each cl In Rg
If cl = "" And cl.MergeArea.Columns.Count = 1 Then
'found = True
cl.Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Exit For
Else
MsgBox "No empty cell to paste to yet..."
End If
Next

MY QUESTION??? This appears to be working except when my search for a visible blank cell gets to a hidden column it retruns to the row below insted of skiping over the hidden column and continuing along the row.
In this code it searches row 10 first say column P is hidden it will search and paste along row 10 untill it gets to column P it will then return and start along row 12 as my copy area is two cells tall. Is there any way to get it skip over the hidden column and stay on the same row till the end of the range?
Thanks in advance!!!!

 

[Lowell In the south]

Could it possibly have somthing to do with haveing the visible cells in the range and not in the selection? I have been unable to set the range and then only select visible cells with in that range??

 

[rikrak]

Try this:

<pre>
Dim Rg As Range
Dim cl As Range
Range("Ag2:Ah3").Copy
Set Rg = Range("A10:z19")
For Each cl In Rg
If cl = "" And cl.MergeArea.Columns.Count = 1 And Not cl.EntireColumn.Hidden And Not cl.EntireRow.Hidden Then
found = True
cl.Select
Exit For
End If
Next
If found Then
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Else
MsgBox "No empty cell to paste to"
End If
</pre>

 

[Lowell In the south]

YES!!!! That works!!! THANKS RIKRAK!!! You just helped save a project I have many hours in!!