Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to clear multiple cells & not lose formulae

    [img]/w3timages/icons/frown.gif[/img]I have several spreadsheets which I use to analyze stocks (approx. 20 at a time, with stock symbol at top of each column) and also to follow their performance, etc. There are greater than 35 rows of data, and I have to manually input data in some of the rows such as price (not all of the rows where I input data are contiguous) and the rest of the rows are automatically filled out as they conatin various formulae that refer to other rows. I also frequently apply macros to select cells which generate their own results & some of these also change the formatting of the cell so that significant points are easily seen.
    The problem is that when I clear the cells (to make room for entering another stock's data) by manually deleting the contents of those cells where I had input the information, it is cumbersome as they are scattered over multiple rows. Or if I select multiple rows by highlighting & then pressing delete, it deletes the underlying formulae also, and/or the format change brought about by a macro is not reversed.
    I tried two solutions: a) I created a macro that deletes the contents of those cells where I input the info, but unfortunately it only operates on the same column where the macro was originally recorded. I copied an entire empty column (i.e. one with formulae, etc. but without any stock info) to a far right, unused portion of my spreadsheet and then created a macro to copy & paste it over a column but unfortunately this also operates only on the same column in which it was recorded!
    How can I do this in an easy and efficient manner so that the rows are quickly made ready for another entry?

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    It was a little hard to follow, but it seems your best option may be to:

    A: Create a Template for addition of new stocks, and use it to add new ones to your existing sheets. Or,

    B: Write some code to identify if there is a formula in the cell or not... I know it's possible, but i'm not sure of what syntax, (i'm learning..) can anyone else help? [img]/w3timages/icons/blush.gif[/img]

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    I do have a template from which I regularly create spreadsheets. But it again boils down to the same issue: if I start with a new spreadsheet, then I have to input the data for those stocks I am still following, or presently own. Its impractical to keep on creating spreadsheet after spreadsheet; besides, the stock data would change daily. So I have a *real* need to clean out some columns every day since I trade actively.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    As I understand your problem you would like to delete cells from a range that are not formulas. Try the following procedure :-

    Select the entire area, Press F5, a Go To box pops up, Click on Special at the bottom of the dialog, then select constants, click OK.

    Now all the constant cells are selected so just press delete.

    You should experiment with the other options in the Special Goto box.


    Good luck

    Andrew C

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to clear multiple cells & not lose formulae

    attach the following Macro:

    <pre>Sub WipeData()

    Dim Cell As Range
    For Each Cell In Selection
    If IsNumeric(Cell.Value) Then
    If Cell.HasFormula = False Then
    Cell.ClearContents
    End If
    End If
    Next Cell
    End Sub
    </pre>


    Select the range where you want to delete the data entered, and then run the macro. If you are doing this often, it may be worth attaching the macro to a button. I like Andrew's solution, but this requires fewer keystrokes, particularly if you attach it to a button. As well, it does not delete text, in case you have label entries to remind you of assumptions or methods of analysis ('deduct net non-cash here' etc)

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    Thanks to both Andrew & dcardno for their responses. Both of them are certainly faster than my method of manually deleting data from the relevant cells! However, a couple of issues remain:
    a) When I created a macro using Andrew's method (to apply to a button to save time), it only ran on the column in which I had originally created it. Is there a way to create a macro in such a way that it may either ask you which column you want to remove the constants from, or I could start of by placing my cursor at the top of the column I want to clear, then run the macro and it would automatically delete the constants from about 35 rows below the initial cell ("ActiveCell").
    I sometimes run macros on certain cells which also change the background color and font color; these changes are not reversed by either Andrew's or dcardno's solutions.
    c) Regarding dcardno's macro, I did apply it to a button and ran it but I would like it to remove text also, besides reverting the format to its original state, if possible (see b above). BTW, I copied the macro from the web page to Excel and it doesn't have the same elegant formatting (it pasted as a single line and I had to break it up using the Enter key) but it does appear to be working the way its supposed to. (Dumb question: what is Dim?)

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    Hi,

    I'll just respond to the BTW in point c.

    If you copy formatted text from a posting, first paste it into Excel. Then copy it again and paste it into the VBE editor.

    In word, copy it into Word, then replace "^l" (manual line breaks) with "^p" (end of para markers, then copy it into Word.

    This is where I think the best way of psoting code is to save it as a txt file, then attach the file to the post. Formatting is protected; people can copy and paste; and long lines of code don't muck up the viewing screen in the form.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    Hi,

    I normally like to post technical solutions, but I won't this time.

    I'd suggest something quite different.

    Put a new sheet in your workbook, say "Data Entry". Clumn 1 might contain all your prompts, and put all your data entry in column 2.

    Then in your calculation sheet, change the data entry cells to read "='Data Entry'!A2" (or whatever cell).

    So then, to clear out all the data entry, just highlight column 2, and press the delete key.


    Now, if you'd really like something technical, why not load the stock prices automatically from the web?

    For a small sample of how this can be done, click Data, Get External Data, Run Saved query- then select one of the queries. You can enter stocks into thw web.

    For an example of how this can be used in a real spreadsheet, look at <A target="_blank" HREF=http://pmacgowan.freeyellow.com/Index.html>portfolio.zip</A>- which is a portfolio manager for use in the Australian stockmarket- it unloads 20-minute delayed quotes into the spreadsheet for your choice of stocks from the Australian Stock Exchange web site, and then maniuplates them (the trial version only supports 5 stocks).

    I've successfully done work along the same line.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    Hi

    Re point A, when recording the macro you should have started the recorder after you selected the range of cells. (You seem to have made the origianal selection part of the macro). Then before running the macro you would need to select a range to which youwould like it to apply. (The macro cannot be aware of which cells you would like it to perform on)

    Re point B, instead of using the Dlete key, goto Edit | Clear | All and that should remove all formatting as well as the values.

    Dim is used to tell VBA that want to reserve memory for the use of variables in your routine.

    Hope that clarifies for you,

    Andrew C

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    When you record a macro, it is recorded to do exactly what you did when you recorded it. So, if you worked on a single column, the recorded macro is going to work on the same column. Yes, it is almost always possible to change a recorded macro to do the same thing on a variable range of cells. However, we would need two thing to help you do this. First, we would need to see the macro that you recorded so that we know what it does. Second, we would need for you to tell us how the macro should determine what range of cells you want it to work on. That might be all of the selected cells, all of the cells in some specific range, all of the cells from the curently selected range to the first empty cell in the first row of the current selection, etc.

    I have modified dcardno's code to work on all cells in the current selection that do not contain formula, and to clear ny formatting.

    <pre>Sub WipeData()
    Dim Cell As Range
    For Each Cell In Selection
    If Cell.HasFormula = False Then
    Cell.Clear
    End If
    Next Cell
    End Sub
    </pre>

    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    Thanks, all. I never knew so many solutions existed!
    a) I tried gwhitfield's way to preserve formatting in the macro and it worked great!
    Regarding Andrew's solution of Edit | Clear | All and the modified WipeData macro by Legare, the problem is they remove all my background colors, etc. and make the cells white (in Andrew's case, just the cells with constants, and in WipeData's case, all the cells)
    c) I have tested a new macro, created by pre-selecting the range of cells I need to clear as suggested by Andrew, and it appears to be working fine. I gave it the name "test" and its like this:

    Sub test()
    '
    ' test Macro
    '

    '
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    End Sub

    I have assigned it to a button. So now I highlight the cells I want to run it on, press the button and it clears them up (some format changes still remain in the cells to which I have applied a macro that changes background color, etc. but I can live with that - I suppose you can't have it all; as mentioned above, if I clear All the contents, then the cells become white and I don't like it).
    I don't know if its possible, though Legare's answer seems to hint that it may be, to reduce this whole process by another step - by just selecting the top of the column I want cleared and the macro would then clear a specified range of cells below that & including that cell (basically I want rows 15 through 48 cleared). Any suggestions?

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: How to clear multiple cells & not lose formulae

    Hi,
    You could try this:
    Sub ClearConstants()
    Dim strColumn As String
    strColumn = InputBox("Enter column to clear.")
    Range(strColumn & "15:" & strColumn & "48").SpecialCells(xlCellTypeConstants, 23).ClearContents
    End Sub
    You can run it from anywhere as it prompts you for which column to clear constants from, and it only does rows 15-48.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    The following macro will work on the cells in rows 15 through 48 starting at the first cell in the current selection and go through the last cell in the current selection. In that range it will clear the contents of all cells not containing a formula and set the cell interior to the default color and leave all other formats unchanged (which is probably not what you want since you say you don't went your background color changed, but don't say what you do want changed).

    <pre>Public Sub Test()
    Dim oCell As Range, oArea As Range
    Dim iFirst As Integer, iLast As Integer
    iFirst = Selection.Column
    iLast = iFirst + Selection.Columns.Count - 1
    Set oArea = Range(Cells(15, iFirst), Cells(48, iLast))
    For Each oCell In oArea
    If Not oCell.HasFormula Then
    oCell.ClearContents
    With oCell.Interior
    .ColorIndex = xlColorIndexNone
    End With
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  14. #14
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    I have tried both Legare's & Rory's methods. They both work but here are the downsides I have noticed so far:
    1) Legare's macro: It removes the background color from the cells with constants, leaving them white. Some of those cells have plum color & some have dark yellow (I have other colors in the sheet as well, but those contain formulae). Can the macro "read" the colors from an adjacent cell (e.g. to the right of the cell whose contents its deleting) and then apply it?
    2) Rory's macro is a beauty but there is one BIG problem I discovered. If I leave the input box empty & instead click on the Cancel button, it deletes ALL columns. YIKES! Can this be fixed?

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to clear multiple cells & not lose formulae

    Yup:

    <pre>Public Sub Test()
    Dim oCell As Range, oArea As Range
    Dim iFirst As Integer, iLast As Integer
    iFirst = Selection.Column
    iLast = iFirst + Selection.Columns.Count - 1
    Set oArea = Range(Cells(15, iFirst), Cells(48, iLast))
    For Each oCell In oArea
    If Not oCell.HasFormula Then
    oCell.ClearContents
    With oCell.Interior
    .ColorIndex = oCell.Offset(0, 1).Interior.ColorIndex
    End With
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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