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


ADVERTISEMENT

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 View Related

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 Upon Value In Row Range

Jul 21, 2006

I'm trying clear the contents of a column range when the cell above this column range is empty. I know how to do this for one cell, but I would like it to work for a rangefrom R15:BB15.

If IsEmpty(Range("r15")) Then
Range("r16:r35").Select
Selection.ClearContents
End If

End Sub

View 5 Replies View Related

Clear Content Based On Contents Matching?

May 25, 2014

I have a column with various values in, string and numeric. The strings I want to keep have "," in and letters one side and "space" on the other. All the cells I want cleared do not have ","

The rest of what I'm using works fine accept this line which forces an error.

[Code]....

View 5 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

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 Of Range Based Upon Values

Dec 17, 2007

There is data(numerical) or blanks in each of the cells in the range H32:O37

I want to clear the contents if they are a duplicate of the previous line.

Looking for a macro hopefully. Found many to delete the whole row but just the specific range based upon the duplicate criteria..As there is data surronding that area do not want to delete whole row just clear that specifc area.

H32:O32 clear contents if same as H31:O31 if any valuse are different then stays the same
H33:O33 clear contents if same as H32:O32 if any valuse are different then stays the same
H34:O34 clear contents if same as H33:O33 if any valuse are different then stays the same
H35:O35 clear contents if same as H34:O34 if any valuse are different then stays the same
H36:O36 clear contents if same as H35:O35 if any valuse are different then stays the same
H37:O37 clear contents if same as H36:O36 if any valuse are different then stays the same

View 9 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 Contents Of Cells Based On Data Validation Selection

Apr 13, 2012

I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.

The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.

This capability should be available for each row through 100.

View 3 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

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

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

Clear Contents Of Column Based On Data In Another Column?

Apr 10, 2014

I need to clear the contents of a columns G and H starting 11th row based on what is there in F column. The macro should check for last non-empty cell in column F starting F11 (assume it finds F30 to be last non-empty cell), then it should clear the contents of G11 to G30, H11 to H30.

View 1 Replies View Related

Automatically Create List Of Column Contents Based On Criteria?

Jan 15, 2014

I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".

V
W
X
Y
Z
AA
AB
AC
...
FB

[code]....

List of Dates Absent

x
x
x
WR

x
x
x

1/23, 1/24

[code]....

View 2 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

VB Code To Clear Contents

Jul 3, 2014

I have a range of cell content to be clear (Value only) but no formula in those cell.

The range is populated in cell H106.
Example cell
if cell H106 = D5:E102 then clear the value value only in cell range D5:E102
if cell C106 = G5:H102 then clear the value value only in cell range G5:H102.

View 8 Replies View Related

VBA To Clear Contents At Specified Intervals

Mar 6, 2014

I think I've had enough of using excel generated macro's as an excuse for decent code. I need to turn this:

Small procedure, function, what have you that will continue this pattern until I hit some kind of indicator to tell it to stop. This just clears the contents from 7 rows, hops down 9 rows and does it again.

View 14 Replies View Related

Clear Contents In Various Worksheets At Once?

Dec 2, 2011

I have a monthy activity where I would like to clear the area B13:P42 on various worksheets in the same spreadsheet. Is there an easier way to do this rather than just recording a macro?

View 5 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 Contents Of Entire Row?

Jan 21, 2012

Macro to clear contents of entire row if any cell in Range A2:A17 is Empty.

View 9 Replies View Related

Clear Contents Of Dropdown Box?

Feb 27, 2012

I am trying to have a macro clear out the contents of a cell before it quits, the cell contains validation data in the form of a list. My goal is to force the user to choose an option every time before exporting a file, the cell contains what will become a part of the filename. I want to make sure the user, usually me, remembers to change it each time rather than accidentally saving over another filename. I had the following code and kept getting a 400 error (nice and specific)

Code:
ActiveSheet.Range("O3").ClearContents

The only way my macro will run without a 400 error is if that line is commented out. Is there any way to clear out the cell and retain the validation data dropdown list?

View 1 Replies View Related

VBA To Clear Contents But Not Header?

Jul 30, 2014

i have a vba that clears content from specific cells, hence somehow it also deletes the header as well

This is my code:

Dim LastRow As Long
Sheets("All").Select
LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("A2:J" & LastRow).ClearContents
Sheets("All").Select
LastRow = Range("AO" & Rows.Count).End(xlUp).Row
Range("AI2:AO" & LastRow).ClearContents

View 9 Replies View Related

Macro To Clear Contents.

Aug 20, 2008

I'm trying to figure out code to have only the contents of cell 'a2' cleared when the contents of cell 'a1' is changed. The issue is that both cells are derived from lists and I don't want the formats cleared.

View 9 Replies View Related

Vba Clear Contents Of Column

Apr 8, 2009

I dont know how to copy an excel sheet into the question.

But assuming the following text starts at CELL C2 (this is just a section of the workbook)

C D E F G

1x55Correct!
2x55Try Again?
3x55Try Again?
4x55Try Again?
5x55Try Again?

This is a worksheet to test multiplication tables. I am trying to create a macro button to clear the contents of column F, for the kids to start again?

View 9 Replies View Related

Macro For Min Value Then Clear Contents

Sep 30, 2009

I need code for a macro that will find a min value in a particular column and based on that min value clear the contents of other cells for that min value.

For example, I have dates in column E such as:

200907
200906
200902
200809
200803
200710
200707

Therefore, I need to find all records for 200707 and clear the contents in other cells.

I can't hardcode the min date because that can change from month to month when new files are created. As can the number of rows.

I'm using Excel 2007.

View 9 Replies View Related







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