Sorry about the confusion- and thanks for the help, I'll try your solution.
Keith

"Tom Ogilvy" wrote in message
...
Hard to follow your explanation, but sounds like your code should hide the
columns/rows you don't want, do the copy and paste, then unhide the
columns.



Dim rng as Range, rng1 as Range
set rng = sheet11.Range("D45:IV45").SpecialCells(xlformulas)

for each cell in rng
if cell.value = 0 then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.Entirecolumn.Hidden = true
end if
Sheet11.Range("D7:IV45").Copy
Sheet7.Range("A1").PasteSpecial xlPasteValues, , , True
if not rng1 is nothing then
rng1.EntireColumn.Hidden = False
End if


--
Regards,
Tom Ogilvy




--
Regards,
Tom Ogilvy


"Keith" wrote in message
...
XL2003
I have the following code, which works fine; it copies a group of visible
cells (containing names) and transposes it on another sheet.

Sheet11.Range("D7:IV7").SpecialCells(xlCellTypeVis ible).Copy
Sheet7.Range("A1").PasteSpecial xlPasteValues, , , True

This works fine, because the cells to the right of the names are blank,
so they show up blank on the destination sheet

Now I need to grab the "scores" for each person, which are in row 45, but
I only want to bring over the relevant range (columns with names). Every
cell in row 45 has a formula (more names are added on an ongoing basis),
so all the cells beyond the last column with a name just evaluate to
zero. If I use the code as-is and just copy over D45:IV45, I get those
extra zeros down the page. I don't want to hide the extra columns in
Sheet11 because other pieces of code add and pull data that the user
needs to see, and that would be a lot of code checking.

Is there a way to limit the copy range above to not only visible cells,
but cells <0? If so, then I assume I could just use an offset to also
grab the scores from the visible columns.

Any advice greatly appreciated!
Thanks,
Keith