Selection Offset

I had a worksheet table with blank rows separating the groups.
I needed to add another column – a formula – but wanted to retain the blank rows for formatting tidiness.

The table after adding the formula column:

Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and hit delete, but for thousands of rows that would be time consuming.

Here’s how I did it:
– Select column C.
– From the Edit menu, select Go To…, then click Special…
– Select Blanks, then click OK

Then I ran a macro which allows me to move the selection over one column.
In this example, I typed 0, 1 for the Input to SelectionOffset.
After the Selection was moved, I hit the delete key.

Sub SelectionOffset()
    Dim strInput As String, str As String, i As Long, bln As Boolean
    Dim strRows As String, strCols As String
 
    strInput = “”
    Do
        bln = False
        strInput = InputBox(“Selection offset by rows, cols” & vbNewLine & _
                “eg. 12, 2”, “Selection offset”, strInput)
        str = Replace(strInput, ” “, “”)
        If str <> “” Then
            i = InStr(str, “,”)
            If i = 0 Then strRows = str Else strRows = IIf(i = 1, “0”, Left(str, i – 1))
            If i = 0 Or i = Len(str) Then strCols = “0” Else strCols = Mid(str, i + 1)
 
            If IsNumeric(strRows) And IsNumeric(strCols) Then
                On Error Resume Next
                Selection.Offset(strRows, strCols).Select
                If Err.Number <> 0 Then
                    MsgBox “Invalid selection offset”, vbExclamation, “Error”
                    bln = True
                End If
                On Error GoTo 0
            Else
                MsgBox “Selection offset is not numeric”, vbExclamation, “Error”
                bln = True
            End If
        End If
    Loop While bln
End Sub
Posted in Uncategorized

14 thoughts on “Selection Offset

  1. Could you not have used an autofilter, selected “0?, F5>Special…>visible cells, Delete?

    -Enjoy your site!

  2. If “” Cells in Column “D” must be blank use this simple macro:
    Range(“D:D”).SpecialCells(xlCellTypeFormulas, xlTextValues).ClearContents

  3. Hi,
    just to extent the proposal of “the Okk”

    Use the formula =IF(C2>0,IF(B2=”T”,2,1)*C2,1/C2) and copy down.
    In cells D3, D5 etc. you got errors #DIV/0!
    Select the column D
    Hit F5, Go To,
    Click Formulas and unclick everything but errors…
    Press OK and then Delete.

    My best regards

    Jiri Cihar
    http://www.dataspectrum.cz

  4. Another approach: Hard code a sort key in Col.E with Excel’s Edit->Fill->Series… Sort on Col. A to group the blank rows. Delete romulas in D and resort on the key.

    Brett

  5. I try and avoid blank rows, I prefer to use the row height to sort the formatting out.
    I like the select special errors approach, Bob has that in his Excel magic book too.
    cheers
    Simon

  6. As an alternative

    =IF ( B2=”” , “” , IF (B2 = “T” ,2,1) * C2 )

    or

    You could just leave the original formula as is and
    use a Conditional Format Font colour of White for
    Blank or Zero value cells in Column D

    or if you wanted to use a macro to insert just the values
    in the non blank rows

    For I = 2 to Columns(1).Find(“*”,,,,,xlPrevious).Row
    If Cells(I,2) = “S” then
    Cells(I,4) = Cells(I,3)
    ElseIf Cells(I,2) = “T” then
    Cells(I,4) = Cells(I,3)*2
    EndIf
    Next I

    or you could just use the same to insert the Formula if
    this is actually needed

    Kind regards

  7. Couldn’t you have filtered column D for all cells equal to zero? Then use Edit|Go To|Special|Visible Cells Only and press Delete.

  8. Neil,

    As long as nobody’s Weighted (col D) evaluated to 0. But you could filter A, B, or C for blanks!

    Brett

  9. You should also be able to just filter a column for non-blanks and use ctrl+enter to enter the formulas. Note that going to visible cells shouldn’t be needed as operations in filtered view only apply to the visible selection.

  10. Hello,

    try this to fill column D in the required rows with the formula in question as per your example:

    Sub Fill_not_empties()
    Dim lz As Long
    lz = Cells(Rows.Count, 3).End(xlUp).Row
    Range(“C2:C” & lz).SpecialCells(xlCellTypeConstants, 23) _
    .Offset(0, 1).FormulaR1C1 = “=IF(RC[-2]=””T””,2,1)*RC[-1]” ‘Column D
    End Sub

    Regards
    Beate

  11. I always use the formula:

    =IF ( B2=”” , “” , IF (B2 = “T” ,2,1) * C2 )

    Works every time & I do it without thinking now.

  12. When I first saw the problem I thought keep it simple. Came up with the formula a Andrew Hunt. Then I scrolled down. I am a Geologist an work with Geotech and Engineers. I like to avoid macros, I find it hard for fellow workers to follow or edit. Often other will asked for my help. I like to keep it simple for them and to predict incorrect inputs. This is the choice I would select, If someone put in the wrong data it will still produce a blank space
    =IF(B1=”T”,2*C1,IF(B1=”S”,C1*1,””))

Leave a Reply

Your email address will not be published. Required fields are marked *