Range with hidden columns filtered question

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
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!!!!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
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

Active Member
Joined
Aug 21, 2002
Messages
255
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,912
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top