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

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

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