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

  • Thread starter Thread starter Keith
  • Start date Start date
K

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
 
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
 
Back
Top