VBA Clear Cell Contents If Criteria Met?

Feb 7, 2013

I have a spreadsheet with data to row 5000. I have column Y that has an "X" in it. I would like some VBA code to look at each row up to 5000, in column Y for the "X". If it is there, clear the cell contents on the current row in columns T, U, and V.

I've tried modifying some existing code (excluding the Y column range of 5000) but keep getting a "Compile error: Wrong number of arguments or invalid property assignments". How do I set the 5000 limit and get this code back on track?

Sub RemoveBankDelay()
n = WorksheetFunction.CountIf(Range("Y:Y"), "X")
For i = 1 To n

[Code]....

View 9 Replies


ADVERTISEMENT

If Cell String Meets Criteria Then Clear Contents From Array On Same Row

Jul 17, 2013

I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A.
The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?

Sub Recalculate()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count

Application.ScreenUpdating = False

For Row = 13 To r

[Code] ........

View 2 Replies View Related

Clear Contents Based On Criteria

Aug 28, 2008

I need some code that will clear the contents of a Row Range(C:T) if either of the contents of cells X and Y = "E"

The code needs to look at cells X17 and Y17 and continue looking until it gets to X216 and Y216

So - If X = E but Y does not, then the row range C:T must be cleared.
If Y = E but X does not, then the row range C:T must be cleared
If both X and Y = E, then the row range C:T must be cleared

If the letter E does not exist in either X or Y, then the row must be left alone

View 13 Replies View Related

How To Clear Cell Contents

Jan 17, 2012

I have the following code where I want to clear the cells.

Code:

Workbooks(strOutputFileName).Sheets(2).Activate
Range("F9:F25,C27:E57,F59:F60,J9:J25,G27:I57,J59:J60,N9:N25,K27:M57,N59:N60,R9:R25,O27:Q57,R59:R60").Select
Selection.ClearContents

When I run this code, where it should select G27:I57, it instead selects G27:N57!!!!

When I manually try to select the G27:I57, the selection jumps to the right to N57.

When I use this piece of code on a different worksheet, it works OK!

View 6 Replies View Related

Clear A Cell's Contents

Mar 7, 2007

Is it possible using vba code to clear a specific cell's contents? I have a workbook that has text boxes that need the linked cell cleared when the workbook opens so the user can start fresh with empty text box's.

View 6 Replies View Related

Automatically Clear Contents Of Cell When Selected

Oct 13, 2009

Is there way to automatically clear the contents of a cell when the user clicks in that cell. Eg, click in "A1" then then "A1" clears automatically.

View 10 Replies View Related

Clear Cell Contents Based On Defined Name

Jan 13, 2010

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.

View 6 Replies View Related

Code To Clear Cell Contents For Rows?

Sep 12, 2012

Small piece of code to clear the contents of

Rows 33 thru 100 , then
Rows 142 thru 209 , then
Rows 251 thru 318 , then upto
Rows 43524 thru 43591 then finish

View 5 Replies View Related

Automatically Clear Contents Of Cell Range

Jan 17, 2013

Is is possible to clear the contents of a cell range if a message box appears stating an error. I have tried the following code but the logic doesn't actually work and am struggling to achieve what I am trying to do.

My code so far is as follows:

Code:

If ActiveSheet.Range("F84") > 0 And ThisWorkbook.Worksheets("PES").Range("D24") = 0 Then _
MsgBox "Your Entitlement is currently 0", vbCritical, "Error"
ActiveSheet.Range("K84:T84").ClearContents

The message box appears fine if the IF statement is true but if IF statement is false, the cell range of K84 to T84 on the current sheet is still cleared..

View 2 Replies View Related

Clear Contents Based On A Selection In Another Cell.

Feb 21, 2007

If I have a pick list, or drop-down list, in cell B21 and one of the options in that list is "Clear", how can I get the contents of cell C21 to be cleared when I select the "Clear" option in B21?

Constraint... Don't want to use VBA.

I am thinking of hiding a formula somewhere other than in C21 that evaluates B21 and does an If Then kind of deal to clear C21.

View 9 Replies View Related

Clear Contents If Cell Doesn't Begin With #

Sep 3, 2008

I am trying to write a piece of vba that clears the contents if the first character in the cell is not #.... if I use "#"*, I get a syntax error

Just figured my syntax needs a slight adjustment to be recognized...

View 9 Replies View Related

Macro To Clear Contents In Every Other Cell Of A Selection

Apr 25, 2008

I need to clear the contents of every other cell in a selection of 5000 rows starting with the first cell. Is there a macro that can do this for me?

View 3 Replies View Related

Cell To Clear Contents Of Group Of Locked Cells?

Feb 22, 2013

I have created a worksheet with many calculations based on user input into several unlocked cells. I would like to create a cell that when selected, would clear the contents of this group of unlocked cells.

View 10 Replies View Related

Clear Contents In Specific Cell And Cells In Next 2 Columns To Right?

Jul 3, 2014

I am currently using this Code to search column1 for a Key Word that is entered into textbox "Kunde" on my userform.

[Code] .....

What i would really like to be Abel to do is :

Search for the cell in column1 with =Kunde.Value

Clearcontents of this cell and the 2 adjacent to the right.

i.e. Word is found in A7

Cells A7:C7 contents are cleared and fill Color returned to default colorindex 0

View 3 Replies View Related

Macro To Clear Contents Of Cells When One Cell Equals To 0 %

Oct 8, 2013

I'm trying to create macro that will clear the contents of the cells in the 4 columns to the left of a cell that = 0 %, as well as the cell that = 0%. For example, if cell F13 = 0 % then the contents of B13:F13 would need to be cleared. The range in which data is being pasted into is B13:F27.

View 4 Replies View Related

Clear The Contents Of Each Cell In A Range Without Losing Formulas

Dec 6, 2006

Is there a way to clear the contents of each cell in a range without losing formulas?

View 9 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?

Jun 1, 2014

i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows

View 3 Replies View Related

VBA To Clear Contents / Change Value Of A Cell Based On If Another Cell Has A Value And Continue L To R

Mar 9, 2013

I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.

I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..

View 9 Replies View Related

Clear Contents Of Cell Based On Comparsion Of Cell Values

Mar 7, 2007

I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col

if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.

Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................

View 4 Replies View Related

Hide Column And Clear Contents Based On A Cell Value In Another Sheet

Mar 19, 2009

I have a workbook with two sheets of data. I want to hide column B of Sheet2 and clear contents of range B2:B50 if the value in A1 of Sheet1 is "a".

View 2 Replies View Related

Clear Cell Contents If Another Cell Equals String

Sep 23, 2013

I have a 700 line spreadsheet and would like to delete the cell next to another cell if it contains a certain string. I.e if A1 to A3 equals "Test" then i would like to delete the contents of B1 to B3, But if A2 equals "Completed" then only B1 and B3 should be cleared.

View 4 Replies View Related

Clear Contents Macro For Merge Cell (Compile Error - Expected End Sub)

Jun 13, 2014

I'm trying to use the "clear contents macro" for merge cell, but I keep receiving this "compile error : Expected End Sub" error.

FYI, I have named my merge cells to "myMergedCells"

[Code] .....

View 3 Replies View Related

If Data Changes In Cell Clear Contents Of Another Cell

Feb 20, 2009

I'm sure this is very easy but I just can't seem to get my head around it

I have data in cells j3 and j4 but when the data changes in j3 I'd like j4 to be cleared of it's contents.

View 12 Replies View Related

Clear Contents Of A Cell Of Row A, If Cell In Row I Is Blank

May 6, 2009

I need to clear the contents of the cell in Row "A", if the cell in Row "I" is blank.

The issue, is there are roughly 1200 excel documents in a folder and subfolders. That is a painful amount of opening workbooks, sort ascending and deleting. Not on my top 10 list of things to do.

If someone could create a code to do this, and loop it through a folder (I can move all the files to a single folder manually, not an issue)

View 6 Replies View Related

Clear Cell Based On Criteria

Apr 18, 2005

I'm trying to write a macro that clears the cell in the range V10:X14 if the value of that cell equals 99.

Sub MyDeleteCell()
For i = 10 To 14 Step 1
For j = 22 To 25 Step 1
If Cells(i, j) = 99 Then Cells(i, j).Clear
Next i
Next j
End Sub

It's giving me a "Invalid Next Control Variable reference" I've tried defining i and j as Integers but that didn't work.

View 3 Replies View Related

Automatically Clear Range Based On Cell Criteria

Jan 9, 2007

I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

rng = Range("F" & Target.Row & ":M" & Target.Row)

If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then

If Target.Cells.Count = 1 Then

If Target.Value = "Yes" Then

Application.EnableEvents = False

rng.ClearContents

Application.EnableEvents = True

End If

End If

End If
End Sub

View 9 Replies View Related

VBA Clear Contents

Feb 5, 2010

i am trying to use clearcontents with the below code but i get an error,


With Sheets(1)
With .Range("J7")
.ClearContents Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
.Formula = "=H7-I7"
.AutoFill Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
End With

View 9 Replies View Related

Clear Contents Of Cells

Sep 10, 2013

I want the function of clear contents to work based on the value of B4. If I run the code with clear content part excel crushes.

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B4") = "Basic" Then ''*** B4 is adropdown ***''
ActiveSheet.Unprotect
'Range("B10,F10,H10").ClearContents ''*** if I run this part the excel crushes ***'''
Range("B10,F10,H10").Locked = True

[Code] .....

View 4 Replies View Related

Clear Contents If 2 Characters Or Less

Feb 10, 2010

I am looking for a way to remove text from a cell if it is 2 characters or less but do nothing if the text is longer than that. Is there a way to do this with either a function or an advanced find?

For more detail, I have a list of middle initials and last names. The cells are either a middle initial by itself like "D." or they are a last name like Jones. I want to go through and delete the cells that have the middle initial and leave the ones with the last name. So if there is a function that will evaluate the cell, and if it is 2 characters or less, then delete the contents. If greater than 3, leave it alone.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved