Set Visible Cells To TRUE

[Kevin H. Stecyk]

Hi,

I am using XL 2003 with O/S XP.

I have a table consisting of several columns and over 100 rows. The table
has a quick filter applied to it, so users can easily filter data.

One column "KeyRange" is set to TRUE or FALSE depending on whether I want to
manipulate that data with other macros. At present, I use the quick filters
to narrow down to the key rows and then set each row to TRUE. I'd like to
do this task by a VBA macro instead. That is, after using quick filters to
select the appropriate rows, use a VBA macro to set all visible cells in
range KeyRange to TRUE and all hidden cells to FALSE. Can someone please
give me some guidance?

Once I have VBA code, I can easily attach a button to the spreadsheet. My
challenge is getting the VBA code to set the appropriate cells to either
TRUE or FALSE.

I look forward to your help.

Regards,
Kevin

 

[Bob Phillips]

This assumes the filtered data is in column F

Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
Set rng = KeyRange.Cells(2, 1).Resize(LastRow -
1).SpecialCells(xlCellTypeVisible)
rng.Value = True
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kevin H. Stecyk]

Bob Phillips wrote...

This assumes the filtered data is in column F

Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
Set rng = KeyRange.Cells(2, 1).Resize(LastRow -
1).SpecialCells(xlCellTypeVisible)
rng.Value = True
End With

Hi Bob,

Thank you for replying promptly.

Using your information, I tried the following:

Sub SetRange()

Worksheets("Sheet1").Range("KeyRange").Formula = False
Worksheets("Sheet1").Range("KeyRange").SpecialCells(xlCellTypeVisible).Formula
= True


End Sub

In my example, I have already defined "KeyRange" as a range on the
spreadsheet. For now, let's assume Sheet1.

My example fails. It fails when the first line where I want to set the
entire range to FALSE. I only want the visible cells set to TRUE. So I
think I need to ensure that all other cells are set to FALSE before setting
the visible cells to TRUE. When running the above code, if a cell is set to
TRUE and is hidden, then does not seem to get set to FALSE.

The second line where I set the SpecialCells to TRUE works just fine.

How do I ensure that the hidden cells are set false?

Regards,
Kevin

 

[Bob Phillips]

Is KeyRange an Excel defined name. If so, it works fine for me, except that
nothing get sets to FALSE.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kevin H. Stecyk]

Bob Phillips wrote...

Is KeyRange an Excel defined name. If so, it works fine for me, except
that nothing get sets to FALSE.

Hi Bob,

Yes, "KeyRange" is an XL spreadsheet local defined name belonging to Sheet1.

Let's assume that KeyRange is A100:A200.

Let's further assume that all cells are visible.

Sub SetRange()

'\ Step 1
Worksheets("Sheet1").Range("KeyRange").Formula = False

'\ Step 2
Worksheets("Sheet1").Range("KeyRange").SpecialCells(xlCellTypeVisible).Formula
= True

End Sub

Using our prior assumptions, we find that all cells are set to FALSE under
step 1, and then all cells are set to TRUE under step 2.

So far, so good.

Next, Let's have another column B100:B200 (let's call this range
"FilterRange") where it is filled with random integers between 1 and 3.

Let's set all cells to TRUE in A100:A200 (KeyRange).

Let's filter the rows that only those rows that equal 1 in FilterRange are
visible.

Running the program again, we find that a) under step 1, only visible cells
are set to false, not the hidden cells; and b) visible cells are set to
true, which is correct.

Thus, the program fails to achieve my objective. That is, under step 1, all
cells should be set to FALSE, regardless if they are visible or not. And
then step 2 should reset the visible cells to TRUE.

Any ideas how to fix step 1 so that all cells are set to FALSE?

Regards,
Kevin

 

[Bob Phillips]

How about this

Public Sub Test()
Dim rng1 As Range
Dim rng2 As Range
Worksheets("Sheet3").Range("KeyRange").Formula = False
Set rng1 =
Worksheets("Sheet3").Range("KeyRange").SpecialCells(xlCellTypeVisible)
Set rng2 = RngNot(rng1, Worksheets("Sheet3").Range("KeyRange"))
rng1.Value = True
rng2.Value = False
End Sub

Function RngNot(RngA As Range, Optional RngB As Range) As Range
'---------------------------------------------
' Using Dave Peterson's interpretation of Tom Ogilvy's
' scratch sheet idea
' Adapted to replace the scratchsheet using Dana DeLouis's
' Validation idea
' Adapted as a function
' Amended to satisfy the need (pointed out by KeepITcool)
' to restore original validation - Validation values passed
' to and from an array
' Amended to add Non-Intersection error handling (KeepITcool)
'---------------------------------------------
Dim Rng As Range, cell As Range, i As Long

If RngB Is Nothing Then Set RngB = RngA.Parent.UsedRange

On Error Resume Next
Set Rng = Union(RngA, RngB).SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not Rng Is Nothing Then
ReDim arr(1 To Rng.Cells.Count, 1 To 14)
i = 0
For Each cell In Rng
i = i + 1
With cell.Validation
arr(i, 1) = cell.Address
arr(i, 2) = .Type
arr(i, 3) = .AlertStyle
arr(i, 4) = .Operator
arr(i, 5) = .Formula1
arr(i, 6) = .Formula2
arr(i, 7) = .ErrorMessage
arr(i, 8) = .ErrorTitle
arr(i, 9) = .IgnoreBlank
arr(i, 10) = .InputMessage
arr(i, 11) = .InputTitle
arr(i, 12) = .ShowError
arr(1, 13) = .ShowInput
arr(1, 14) = .InCellDropdown
End With
Next cell

Rng.Validation.Delete
End If

Union(RngA, RngB).Validation.Add 0, 1

On Error Resume Next
Intersect(RngA, RngB).Validation.Delete
On Error GoTo 0
Set RngNot = Union(RngA, RngB). _
SpecialCells(xlCellTypeAllValidation)
RngNot.Validation.Delete
If Not Rng Is Nothing Then
For i = LBound(arr) To UBound(arr)
With Range(arr(i, 1)).Validation
.Add Type:=arr(i, 2), AlertStyle:=arr(i, 3), _
Operator:=arr(i, 4), Formula1:=arr(i, 5), _
Formula2:=arr(i, 6)
.ErrorMessage = arr(i, 7)
.ErrorTitle = arr(i, 8)
.IgnoreBlank = arr(i, 9)
.InputMessage = arr(i, 10)
.InputTitle = arr(i, 11)
.ShowError = arr(i, 12)
.ShowInput = arr(1, 13)
.InCellDropdown = arr(1, 14)
End With
Next i
End If
End Function

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kevin H. Stecyk]

How about this
...

Hi Bob,

I learned that simply cycling through the values seems to work. That is,

Sub SetRange()

Dim rnCell as Range

'\ Step 1
For each rnCell in Worksheets("Sheet1").Range("KeyRange")
rnCell.Value = False
Next rnCell

'\ Step 2
Worksheets("Sheet1").Range("KeyRange").SpecialCells(xlCellTypeVisible).Formula
= True

End Sub

Whether the cells are hidden or visible, they are set to FALSE in Step 1.
Then, Step 2 sets the visible cells to TRUE.

Thank you for your help!

Best regards,
Kevin