Prev: problems with a localconnection to a cub file
Next: Dlook up Access
From: ALV on 30 Jan 2007 11:06
I know you can select visible cells with:

Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Select

Is there a way to select the opposite of that? I need to set the hidden
cells values to null.

Thanks.
From: Allllen on 30 Jan 2007 11:13
you can't select a cell if it is hidden...

what are you trying to do?
--
Allllen


"ALV" wrote:

> I know you can select visible cells with:
>
> Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Select
>
> Is there a way to select the opposite of that? Just selecting the hidden
> cells.
>
> Thanks.
From: ALV on 30 Jan 2007 11:17
Thanks Allllen.

I want to copy a ListObject to an array (C#) where all hidden cells values
are set to null.

I can do it by looping over all rows, but its extremely slow.

I'm hoping I can copy the range, set hidden rows to null and use get_Value
to put it in an array quickly.


"Allllen" wrote:

> you can't select a cell if it is hidden...
>
> what are you trying to do?
> --
> Allllen
>
>
> "ALV" wrote:
>
> > I know you can select visible cells with:
> >
> > Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Select
> >
> > Is there a way to select the opposite of that? Just selecting the hidden
> > cells.
> >
> > Thanks.
From: Gary''s Student on 30 Jan 2007 12:07
Try:

Sub hidden_stuff()
Dim rHidden As Range
Set rHidden = Nothing
For Each r In Selection
If r.FormulaHidden = True Then
If rHidden Is Nothing Then
Set rHidden = r
Else
Set rHidden = Union(rHidden, r)
End If
End If
Next
If rHidden Is Nothing Then
Else
MsgBox (rHidden.Address)
End If
End Sub

--
Gary's Student
gsnu200703


"ALV" wrote:

> I know you can select visible cells with:
>
> Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Select
>
> Is there a way to select the opposite of that? I need to set the hidden
> cells values to null.
>
> Thanks.
From: ALV on 30 Jan 2007 13:39
Thanks Gary.

When I run this, cells in hidden and filtered rows don't have FormulaHidden
set to True, so nothing is selected.

In my current code I run through the range row-by-row and check if EntireRow
is hidden, but this kills performance. On data sets over a hundred thousand
rows it can take a half hour.


"Gary''s Student" wrote:

> Try:
>
> Sub hidden_stuff()
> Dim rHidden As Range
> Set rHidden = Nothing
> For Each r In Selection
> If r.FormulaHidden = True Then
> If rHidden Is Nothing Then
> Set rHidden = r
> Else
> Set rHidden = Union(rHidden, r)
> End If
> End If
> Next
> If rHidden Is Nothing Then
> Else
> MsgBox (rHidden.Address)
> End If
> End Sub
>
> --
> Gary's Student
> gsnu200703
>
>
> "ALV" wrote:
>
> > I know you can select visible cells with:
> >
> > Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Select
> >
> > Is there a way to select the opposite of that? I need to set the hidden
> > cells values to null.
> >
> > Thanks.
 | 
Pages: 1
Prev: problems with a localconnection to a cub file
Next: Dlook up Access