Improve code that checks a range of names - currently using Activate

[Doug Glancy]

I have a spreadsheet with some hidden helper columns. One of these columns
is titled "Delete Row Allowed" and contains TRUE or a blank. True means
that the yes, the row can be deleted. In the spreadsheet, I've defined a
name, "boolDeleteItemAllowed" which refers to "=MySheet!$G1". Thus, the
name always tells whether the currently selected row can be deleted.

In my code I've got the following function, which checks whether row
deletion is allowed. It checks all the rows in the current selection. It
activates one cell in each row to do this. I haven't used Activate in code
for some time, but I'm not sure how to get the relative reference in each
row without it.

Function DeleteRowAllowed() As Boolean
Dim cell As Range
Dim rngActiveCell As Range

DeleteRowAllowed = True
With Workbooks("MyBook.xls").ActiveSheet
'need to set this back at end
Set rngActiveCell = ActiveCell
'check one cell in each row
For Each cell In Selection.Columns(1).Cells
cell.Activate
If CBool(.Names("boolDeleteRowAllowed").RefersToRange.Value) = False
Then
DeleteRowAllowed = False
Exit Function
End If
Next cell
'set back to original ActiveCell
rngActiveCell.Activate
End With
End Function

I don't think it's a big deal in terms of efficiency. As soon as it hits a
blank row it exits and I'll add ScreenUpdating = False. But I am curious if
there's a way to do it without Activate and still keeping the defined names
in the worksheet.

Thanks in advance,

Doug

 

[Norman Jones]

Hi Doug,

I am not certain that I have understood
your requirement, but, perhaps, try:

'=========>>
Function DeleteRowAllowed() As Boolean
Dim Rng As Range

Set Rng = Selection.EntireRow.Columns("G:G")
DeleteRowAllowed3= _
Application.CountIf(Rng, "False") > 0

End Function
'<<=========

 

[Doug Glancy]

Norman,

Thanks, but that's not it. In each row, the cell value in column G could be
True or not. Some rows, basically detail rows in a bid, can be deleted,
others can not. If multiple rows are selected and any of the values is not
True then the function must return False.

I may be making it more complicated than necessary by referring to the
name - I could do something like what you've done. I am interested though
in keeping as much of the logic in the worksheet as possible, and so would
like to use the name.

Thanks,

Doug

 

[Norman Jones]

Hi Doug,

Since you define the right to delete a row
according to the value of the corresponding
column G cell, why not use this in your
function rather than a derived condition:

'=========>>
Function DeleteRowAllowed() As Boolean
Dim Rng As Range
Dim rCell As Range
Dim aCell As Range

Set Rng = Selection

For Each rCell In Rng.Columns(1).Cells
Set aCell = Application.Intersect(rCell.EntireRow, _
Rng.Parent.Columns("G:G"))
If aCell.Value = "False" Then
DeleteRowAllowed = False
Exit Function
End If
Next rCell

End Function
'<<=========

 

[Doug Glancy]

Norman,

Upon reflection, I think that does make the most sense. I'll give the
column a name to make the function more robust.

Thanks,

Doug