Selecting an offset of a range of visible cells, but only those <>0?

[Keith]

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(xlCellTypeVisible).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

 

[Tom Ogilvy]

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

 

[Keith]

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