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

Excel Programming >> 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(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

Excel426

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

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

Excel Programming >> Selecting an offset of a range of visible cells, but only those <>0? Sorry about the confusion- and thanks for the help, I'll try your solution.
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
>
>

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