# Thread: find which cell to use

1. ## find which cell to use

Hi All,

I have the following formula in G5 (and similar in G6 all the way down to G78):
=IF(AND(ISNUMBER(E5),ISNUMBER(E6)),OFFSET('Sheet2' !\$C\$5,E5-1,E6-1,1,1),IF(ISNUMBER(E5),0,""))

So the formula is looking not only at E5 (same row) but E6 (next row) to compute a value using OFFSET. Only if E5 and E6 are both numbers is the calculation with OFFSET performed.

If only E5 is a number, than the value 0 is returned. Presumably this was for the case when row 5 was the last row and there was nothing after it. If neither the current row's E value nor the next row's E value were numbers, than the entire row was actually blank, so I wanted "" returned.

Worked fine so far.

However, it might be easier to use the spreadsheet as a whole if one did NOT assume that if the next row was blank that all subsequent rows are also blank. For example, one might delete the contents of row 6 but row 7 (or row 8 or row 9...) might actually have valid values. Therefore, in the above formula, I would want the 3rd arg to OFFSET (E6-1) to refer to the next row, if any, that actually has a value in column E. So If E6 is blank, then the 3rd arg should evaluate to E7-1; if E7 is blank, then evaluate to E8-1. If there are no entries in col E after E5, then E5 is really the last row, so G5 should result in 0.

I think there are other ways of accomplishing what I need to do but I'm going to try this first.

TIA

Fred

2. Fred,

What I'd do is to enforce the deletion of rows via a Macro. That is if a row is cleared the macro would delete the entire row automatically.

Example: Note code goes in the sheet module it applies to!
Code:
```Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Code to delete entire row if entire row is cleared via clear contents, etc.

Dim isect As Range
Dim lColCntr As Long
Dim lMaxCol As Long

Set isect = Application.Intersect(Range("A1:A8"), Target)

If isect Is Nothing Then
'Message for test usage comment out for production.
MsgBox "Ranges do not intersect"
Else

If ActiveCell.Value = "" And _
Cells(ActiveCell.Row, 1).End(xlToRight).Column = Application.Columns.Count Then
'***Prevent following code from refiring Change Event ***
Application.EnableEvents = False
ActiveCell.EntireRow.Delete
Application.EnableEvents = True '*** Reset Events ***
End If    'If ActiveCell.Value = ""

End If      ' If isect Is Nothing Then

End Sub```
Example after row 5 is cleared via Clear Contents.
ForceRowDeletion.JPG

Of course, you'll need to adjust the range in the code but you only need to include Col A. I'd suggest a dynamic range name DynamicRangeName.JPGwhich will adjust automatically as rows are added/deleted and you don't have to worry about changing the code.
Initial Code change required
Code:
`   Set isect = Application.Intersect(Range("DeleteRange"), Target)`
Note: with this code in effect you CAN NOT insert rows in the used range as they would be immediately deleted by the macro. HTH

3. HI RG,

Thanks for the idea. I'm hoping to do this w/o VBA since the user of the spreadsheet will be on a MAC and I have no idea what her security settings would be or if she'd want to fool around with them. I have another approach that would work but this was the approach I wanted to try first to see if it was possible to overcome the "little" detail as described in my original post.

Fred

4. Hi RG,

I am posting a new thread with my approach to "deleting" rows - this was the other approach I mentioned in my previous response to you. The thread subject is "fill rows".

Check it out.

Fred

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•