VBA: Cel Naming or Reference

    I'm running a macro that clears all the cells in a particular column. What I'd like to do is avoid clearing the sum() formula in one of the totals at the bottom. Since this macro references many different sheets with different length's it's too hard to reference the cel with it's address. Is there a way to name this particular cel and reference this in VBA code?

    Hi D, and welcome to the Forum.
    How about some code that saves the sum formula, clears the column, then replaces the formula?
    You would have to name the cell (ie "test") using Insert-Name-Define that contained the formula that you want to save or just reference the cell address (ie B10)


    Sub test()
    z = Range("test").Formula
    Range("b:b").Clear
    Range("test") = z
    End Sub


    or


    Sub test()
    z = sheets("sheetname").Range("b10").Formula
    Range("b:b").Clear
    sheets("sheetname").Range("b10") = z
    End Sub

    If it's always the last entry in its column, then instead of clearing the entire coumn, just clear down to that entry with a line like:
    Range("A1:A" & Range("A65536").end(xlup).row-1).clear
    replacing the A with your column.
    or if it's easier for your macro
    range(cells(1,c),cells(65536,c).end(xlup).offset(-1,0)).Clear
    where c is the column number you want to clear.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login