How to clear multiple cells & not lose formulae

Topic

New Reply

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?

Reply | Quote

Replies

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?

Drk.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

attach the following Macro:

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

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)

Reply | Quote

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?)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Rory, there is one BIG problem I discovered with your macro. If I leave the input box empty & instead click on the Cancel button, it deletes ALL columns. Can this be fixed?

Reply | Quote

sorry! I should have tested that possibility too!
Try:
Sub ClearConstants()
Dim strColumn As String
strColumn = InputBox(“Enter column to clear.”)
if strColumn = “” then exit sub
Range(strColumn & “15:” & strColumn & “48”).SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub
That should fix it.

Reply | Quote

Yup, its working fine now. Just for my education, in the 5th line of your macro, the numeral 15 is followed by a colon ( whereas the numeral 48 is not. What’s the significance of this? And if I were to modify the macro to adapt to a different number of rows, would I have to follow the same pattern (meaning, the first number to be followed by a colon and the second not)?

Reply | Quote

Excel is expecting a statement like:
Range(“A15:A48”)
which is what the code is effectively creating – strColumn is the column letter so
strColumn & “15:” & strColumn & “48”
evaluates to “A15:A48” (assuming you’ve chosen column A!)
So if you need to change the rows, you simply change 15 to your new start row and 48 to your new end row.
I hope that’s clear? If not, let me know and I’ll try to translate my usual rambling into English!

Reply | Quote

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).

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

Reply | Quote

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?

Reply | Quote

Yup:

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

Reply | Quote

That was quick, Legare. Can you make your macro read the cell color AND font size&color from the adjacent cell and apply it? (I had forgotten to mention about the font – sorry!)

Reply | Quote

Yup again:

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
            With oCell.Font
                .Size = oCell.Offset(0, 1).Font.Size
                .Color = oCell.Offset(0, 1).Font.Color
            End With
        End If
    Next oCell
End Sub

Reply | Quote

The macro appears to be working fine. Just for my education, the offset values of 0,1 – the first one refers to the row and the 2nd to the column?

Reply | Quote

Yes, the first value is the number of rows, and the second is the number of columns, either of which can be negative.

Reply | Quote

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 portfolio.zip– 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.

Reply | Quote