Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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
  •