Selecting an offset of a range of visible cells, but only those <0?
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
|