Free Microsoft Excel 2013 Quick Reference

Clear Cell Contents

Hi,

I was wondering if it was possible to clear the contents of a range of cells in one row, if one of the cells in that range equals a certain value.

For example if cell C2 = "THING", clear the contents of cells C2:G2.
I need this to go through a range such as from C2:C1000 and perform that function each time C2,D2,E2,etc equals "THING"

Thanks to anyone who can help me out on this one!


Post your answer or comment

comments powered by Disqus
I have this VBA code to clear cells content, but it is not clearing the
content...

Set wkSheet = Worksheets(sName)

For iCount = 53 To 92
wkSheet.Cells(nRowNumber, iCount).Clear
Next

Why?

Excel allows you to clear cell contents but it also clears formulas. We
sometimes need to clear cells to produce mockup copies of financial
statements that need to be blank in order to enter new numbers from year to
year. Too much time has to be spent in determing which cells have formulas
(which we want to keep) and which do not. We need another option to
accomplish this. We use Microsoft Office Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Here i am again..
is there any way to clear cells content only if it doesn't contain a formula?
In other words, since I need to blamk a range in a sheet, I want also be sure that if there is a cell in the range that contains a formula, it is not blanked out...

thanks
*pimar

.Hello
I have names in column A which are duplicated in continuous rows until the next name and so on. How can I get the first name and then delete the same name in the rows below (clear cell contents).

The goal is to have the name appear once in column A until the next name, there can be X amount of rows for the same name that would have to remain.

This:
Rick Data1 Data2 Data3
Rick Data4 Data5 Data6
Rick Data7 Data8 Data9
John Data1 Data2 Data3
John Data4 Data5 Data6
etc.

To be like this:
Rick Data1 Data2 Data3
. . Data4 Data5 Data6
. . Data7 Data8 Data9
John Data1 Data2 Data3
. . Data4 Data5 Data6
etc.

Hello,

I'm trying to clear cell contents based on a defined name given to a set of various cells in my worksheet. The cells are not continuous, but since they're given that defined name, I don't think it matters.

The defined name is listed as "CommentsFields".
The worksheet name is listed as "QPRForm_V6"

I found a similar thread, but cannot get it to work with what I'm looking for. I'm relatively new to writing VBA/macros, so I'm not sure what to do.

Any ideas are greatly appreciated!

I have this VBA code to clear cells content, but it is not clearing the
content...

Set wkSheet = Worksheets(sName)

For iCount = 53 To 92
wkSheet.Cells(nRowNumber, iCount).Clear
Next

Why?

Hi Guys

I have a combobox that gets populated from a Sheet in Excel, that is constantly changing. This combobox was made to "Save" the most recent searches the user has saved into the sheet for future use.

I am trying to add the Delete Search Function, as to where the user will select an item from the combobox and then press the "Delete Search Button" and the item from the combobox will be deleted along with the cell content in the Sheet2 where the combo box was taking it from. I have tried sveeral thigns but nothing seems to work.

I have this code:


	VB:
	
 CommandButton24_Click() 
    If ComboBox1.ListIndex = -1 Then 
        MsgBox " Please select a search item to delete" 
    Else 
        If MsgBox("Are you sure you would like to delete this search string?", vbYesNo = vbYes) Then 
            With Me.ComboBox1 
                .RemoveItem .ListIndex 
                .Value = "" 
                Dim ws As Worksheet 
                Set ws = Worksheets("Sheet2") 
                Dim Rw     As Long 
                 
                With Me 
                    Rw = Me.ComboBox1.ListIndex + 5 
                    ws.Cells(Rw, 2).Clear 
                    ws.Cells(Rw, 1).Clear 
                End With 
            End With 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be appreciated.

Thanks.

Dear Everyone,
I have 6 Sheets where users can enter the data.I want excel to Automatically clear the contents of the cells A9:CY58 (in each Sheet) if the user changes the Year value in cell C5. Aslo that the Formulae used in some of the cells in the range A9:CY58 Shoud NOT be deleted/cleared. Only the data that has been entered by the user need to be cleared, keeping the formatting intact.
I would appreciate your kind help.
Thank you.

Lok

Thank you in advance for any assistance that may be offered! I rarely use macros and have never played with Visual Basic, so please be patient with my ignorance!

I need to be able to clear the contents of a small range of cells from within another function such as an "IF" statement. I thought this would be easy, but in fact, has turned out otherwise. To my dismay, I found out you can't run a macro from within a function either (which would have made the problem trivial!). Using a conditional format to "hide" the cell contents is not really an option as I need to eliminate the text so certian calculations are not performed.

Any assistance would be greatly appreciated!

Hi,

I'm a newbie to Excel VBA. At the moment, I'm trying to write a macro which aims to iterate each cell in a range and clear the cell contents if the cell is (a) nonblank and (b) nonnumeric. I've gone through previous posts on this topic, and put together a macro as below.


	VB:
	
 ClearEmpty() 
     
    Dim wb As Workbook 
    Dim ws As Worksheet 
    Dim rng As Range 
     
    Set wb = Application.Workbooks(1) 
    Set ws = wb.Sheets(1) 
    Set rng = Range("K4", "HI4") 
     
    For Each cell In rng.Cells 
        If Application.WorksheetFunction.IsBlank(cell.Value) And Application.WorksheetFunction.IsText(cell.Value) Then 
            Cell.ClearContents 
        End If 
    Next cell 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, when I run this macro, I got an error message:

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Does anyone konw what went wrong with my codes? Thanks.

I want to clear the contents of cells with no color. When I run the following code, I get "Cannot change part of a merged cell." error. Any suggestion would be greatly appreciated. Thank you.


	VB:
	
 Range 
For Each c In ActiveSheet.UsedRange 
    If c.Interior.ColorIndex = xlNone Then c.ClearContents 
Next c 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have been searching on the site trying to find out how to clear a range of data. I would like to clear the contents of columns A - J beginning with row 2 to the last data entry in column A. This would be for two different tabs - Data1 and Data2.

Also - How would you write the code to delete specific cells on two different sheets. Lets say on Sheet 1 you want to clear cells a5-a8 and c9 and on sheet 2 you want to clear cells b4-b7 and b12.

Thanks.

Hi

I am using this macro which deletes the entire row if cell contents of columns M,N,O are empty.

Code:
Dim cell As Range, rng As Range, i As Long, LastRow As Range
  
LastRow = ActiveSheet.Range("K1511").End(xlUp).Row
Set rng = ActiveSheet.Range("M11:M" & LastRow)

For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "" _
And LCase(rng(i).Offset(0, 1).Value) = "" _
And LCase(rng(i).Offset(0, 2).Value) = "" _
Then rng(i).EntireRow.Delete
Next i
However, i need to alter this to clear the cell contents of range E:W for a particular row that has cells in columns M,N,O empty.

I can't figure out how to do that.

any help please ?
thank you
andy

Hi there,

I have a 52 sheet workbook and I would like a macro to clear the contents of
cells starting at row 2 onwards for all 52 sheets. However, some columns
contain formulas which I would like to preserve.

Any ideas would be gratefully received.

Many thanks

I'm using the following code within a Worksheet_Change function to
lock/clear and unlock a range of cells. There's also another bit of code in
the function that forces uppercase for cell F3.

If I type "YES" into cell F3 then the cells unlock just fine, but there's a
problem when I try to clear the cell contents. If I select the cell and
press "Delete" the contents ("YES") disappear but the Change event isn't
triggered and the range D1016 don't lock. However, if I select the cell,
press Backspace then Enter then the Change event triggers and D10:d16 lock
just fine.

So it seems that the Delete key press doesn't constitute an Event. Is that
normal? Can anyone think of a workaround?

Using Excel 2003.

If Target.Address = "$F$3" And UCase(Target.Value) = "YES" Then
ActiveSheet.Unprotect Password:="password"
With Range("$D$10:$D$16")
.Locked = False
End With
ActiveSheet.Protect Password:="jess"
ElseIf Target.Address = "$F$3" And UCase(Target.Value) "YES" Then
ActiveSheet.Unprotect Password:="password"
With Range("$D$10:$D$16")
.ClearContents
.Locked = True
End With
ActiveSheet.Protect Password:="jess"
End If

Thanks in advance.

Grahame

I am using Excel 97, and I frequently use if statements that put a blank
(i.e. "") in the cell display when the statement is true or in some cases
when it is false. If I copy the column and paste special/values, the cells
that had the "", even though they look blank and no longer contain a formula,
they still are not completely empty in that a control down arrow goes to the
total end of the array instead of to the next "non-blank" cell. If I do a
clear contents on the "blank" cells, then control down arrow goes to the
correct, next non-blank cell. How can I automate clearing the contents of
the "blank" cells without clearing the contents of the non-blank cells I am
trying to preserve?? Is there a workshhet function that can perform an
action, like clear contents, on an adjacent cell, or does this have to be a
VB subroutine??
--
Thanks for your help. JDD 20060421

I am trying to clear the content of some "data" cells when I close the excel
document, without having to go to "edit,clear,content" route. Is this
possible?

I have a worksheet that has a static range (B7:BW206) of cells that contain employee related information. When a user needs to remove an employee from the report and the corresponding row data along with it, I would like to have a user select a row or rows and then click on a button that will run the following steps:Have a "Yes/No" message box appear prompting the user if they want to proceed with action.If "Yes", then based on the rows selected within range B7:BW206, clear the cell contents of the rows from columns D:M and O:BW. I have formulas in columns B,C and N and DO NOT want the contents cleared. For selecting the rows to run the "Clearing Contents" process on, I'm not sure how to "flag" what rows that should be cleared. For example, should there be another column added (Column A?) where user would enter a "X" in the cell that would indicate that the macro should run on that row? Or if there is a better solution, I'm open to any suggestions.After the row cells are cleared, I would like to Sort the Range (B7:BW206) with first sort criteria = Level (currently Column E) - Ascending, then by Level Sort (currently Column C - hidden) - Ascending. Then finally, hide rows that have no Employee name. Currently have code for that in a seperate button. Code is listed below.

	VB:
	
 CommandButton11_Click() 
    Application.ScreenUpdating = False 
    Dim i As Integer 
     
    For i = 7 To 206 
        If Cells(i, 4).Value = "" Then 
            Rows(i).Hidden = True 
        End If 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I attached my sample workbook for reference. I appreciate any help that can be provided and contact with any ?'s.

I have 5390 formulas in a worksheet that I need to do a find and replace on.

Every time I do the find and replace it clears the cell contents.

A sample formula is: =(SUMPRODUCT(($F$19:$F$31=Options!$BD8)*($C$19:$C$31="No")*($G$19:$G$31),(AH$19:AH$31)))*(220/12)

The Find replace I have tried is:
FIND *(220/12)
REPLACE

ie. Replace is blank

Have even tried 2 step process of:
1) FIND = REPLACE AAAA

2) FIND *(220/12) REPLACE

Would like to know how to do this in Excel itself or via VBA

Thanks in advance

im trying to write into a macro the code to clear the contents of a range of cells but only want to delete the numbers, not the formats that are associated to the cells (such as decimal places, fill colours etc.)

anyone know how?!

thanks

I have several columns of data in a worksheet (A1:I200), that is hard coded to another worksheet ie cell A1 = Sheet2!AB1, A2 = Sheet2!AB2 and so on

Sometimes there is no data in the source cells which is resulting in many blank rows in the destination worksheet.

I found the following macro that does delete blank rows, but the entire row must be empty for it to work

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I am able to get rid of the formula’s by doing a paste value, however the cells are obviously not truly empty as the macro is not working. (If I manually select the cells and press the delete key the macro works)

Does anyone know a way of a macro that I can use to clear the cell contents of these cells, or any suggestions on how to get rid if these blank unwanted rows?

Thanks
Karen

I am using Excel 97, and I frequently use if statements that put a blank
(i.e. "") in the cell display when the statement is true or in some cases
when it is false. If I copy the column and paste special/values, the cells
that had the "", even though they look blank and no longer contain a formula,
they still are not completely empty in that a control down arrow goes to the
total end of the array instead of to the next "non-blank" cell. If I do a
clear contents on the "blank" cells, then control down arrow goes to the
correct, next non-blank cell. How can I automate clearing the contents of
the "blank" cells without clearing the contents of the non-blank cells I am
trying to preserve?? Is there a workshhet function that can perform an
action, like clear contents, on an adjacent cell, or does this have to be a
VB subroutine??
--
Thanks for your help. JDD 20060421

I am trying to clear the content of some "data" cells when I close the excel
document, without having to go to "edit,clear,content" route. Is this
possible?

Hi,

I have this code from http://www.mrexcel.com/archive/VBA/19248.html
This does all the things I required, except clear the contents of the cell B7 when "BPS" is selected in B6.
Following is the code I am using:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.Address = "$B$7" Then
If [B6] = "BPS" Then
ActiveSheet.Unprotect ("PASSWORD")
[B7].Locked = False
[B7].ClearContents
ActiveSheet.Protect ("PASSWORD")
MsgBox "Sorry, this is for ITS only" & vbCrLf & _
"", 64, "Access into cell B7 not allowed."

End If
End If
End Sub
Pls help me ensuring that cell value in B7 is cleared whenever B6 contains BPS...thanks


No luck finding an answer? You could always try Google.