Clear Data If Criteria Not Met?

Aug 4, 2012

I have a workbook that has lots of data that I dont need. In column B if the data dont start with the letter M/T/W/F/S I need the contents clearing.

View 1 Replies


ADVERTISEMENT

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

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

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

Clear All Criteria In All Autofilters On A Page

May 22, 2009

I have searched these boards (and found wonderful ideas), but can't find a better solution.

Is there a quick way to clear all the autofilters on a page (or all of them in 1 row)?

I am using this code, but it is very slow.

For i = 1 To 21
Selection.autofilter Field:=i
Next i

View 9 Replies View Related

Clear Cells Meeting Criteria

Nov 1, 2006

I would like to seek help on how to delete a cell that has data in it and that has colourfill. E.g. Cell A1 shows "Occupied" and Cell A1 has a blue colour fill.

Need help on how to delete that cell's data and remove the blue colour without having to manually do so.

View 4 Replies View Related

Clear Rows Meeting Multiple Criteria

Dec 13, 2006

In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?

For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "PA" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "TX" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next

View 5 Replies View Related

Clear/Delete Cells Meeting A Date Criteria

Oct 1, 2006

i created a work sheet with the help of the wonderful people on this forum. my boss loved it! one problem they now want it to automaticly take out a date and time of the cells when the date rolls around again.

example:

12/25/05 late 00:20:06 when 12/25/06 rolls around they want the system to automaticly see it and delete it out with the amount of time they were late or sick ect.... or if the sup was out that day and comes in the next day the system will see that the date has past and will up date the info. the other catch is i need it to move everything up one space when it deletes somthing. i want it to read j18 and k18 as one and so through j and k 40 same for l and m n and o ect.... i attached the sheet

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

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

Macro To Clear Row Data Then Shift Remaining Data Up To Empty Rows?

Apr 15, 2014

I'd like to have a sheet with multiple columns of data (say A thru K for instance.). Id like to reserve column A for ONLY imputing an X. The rest of the columns b-K would have data in the cells. I'd like to have a macro that when it saw an X in column A, would copy all of the data in cells B-K in that row, paste it into the next empty row of a second sheet (for history tracking), then go back to the original sheet and continue looking for additional "X"'s and repeat. Once all of the X's were copied, it would "clear" (Not delete because some of the cells would have formulas in them that would need to remain for future use.) the cells based on the "X" then finally move all of the remaining data up to the empty rows to fill in the empty rows. This last piece would be more for esthetics to have a clean looking sheet.

View 1 Replies View Related

Clear Data Depending On User Input And Move Cells Forward Without Deleting Data Table

Oct 25, 2012

I have an month input in cells B2 (user can enter values from 1-5)

I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.

So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.

So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.

1
2
3
4
5

£11
£133
£29
£193
£100

[Code] ........

View 3 Replies View Related

Macro To Clear Some Data Within A Cell But Leave Other Data

Dec 24, 2009

What i want to do is clear some data within a merged cell but leave the rest.

I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......

View 7 Replies View Related

Clear Old Data And Add New Data Every Month, The Named Range Changes

Dec 13, 2008

I have 30 worksheets in a workbook in which the first sheet is called summary. The summary sheet has 29 formulas with all named ranges, so one named ranged per worksheet. The issue I have is when i clear old data and add new data every month, the named range changes on some worksheets not pointing to the correct range as previous month file even if I have $

View 9 Replies View Related

Clear Data ....

Feb 15, 2007

I have a huge amount of data that I use VBA code to calculate totals. I have a macro 'reset to zero' which states a cell contacts should be zero. ie G10 = 0

For some reason when I rerun the macro it adds the data again instead of clearing the first total. For example instead of G10 totaling 19 it now shows 38. But if I close the file and rerun the macro it is right.

View 9 Replies View Related

Move / Clear And Add More Data

Aug 11, 2014

I need a macro to take data from A2:F47 and clear this data. Then move it to A49:F235. I will be doing this several times and I want to save all of the data to A49:F235. I'm very new to Excel and VBA. I can move the data and clear it, but it writes over the data already there in A49:F235.

View 4 Replies View Related

Clear All Data Macros

Jun 4, 2007

I have 2 different Clear all data Macros
I will post the 2 different Macros now, then explain a slight modification needed if Possible;

Sub clear()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow < 3 Then
MsgBox "Nothing to clear!"
Exit Sub
End If
Range("A3:A" & Rows.Count).EntireRow.ClearContents
End Sub

Clear Macro 2

Sub clearalldata()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow < 5 Then
MsgBox "Nothing to clear!"
Exit Sub
End If
Range("A5:A" & Rows.Count).EntireRow.ClearContents
End Sub
Basically can the following be added as a fail safe.
I assign a button to each macro (which are on different sheets in workbook).
If I click on say the assigned macro button called "Clear All" for 1 of the macros, can a pop up box appear?

And say, "are you sure you want to proceed" or it could say "Clear All" and have a "yes" and "No" check box?

Quite simply, I've accidentally clicked on these a few times
If this was included in the macro, then I wouldn't loose all my data accidentally

View 9 Replies View Related

Clear Data Macro

Jul 5, 2009

I've reformatted the spreadsheet, and now some of the data are in merged cells.

The code lnow ooks like below, (Which I thought would work) but it doen't work ,,, and I'm not sure why

Sub Clear_Risk_Data()
If Range("J5:K5,D12,G11:H11,M11:O11") = Empty Then
MsgBox "No data to Clear."
Else
Range("J5:K5,D12,G11:H11,M11:O11").ClearContents
MsgBox "All Data Has Been Cleared", vbInformation
End If
End Sub

My sheet (An excel 2007 macro enabled version), I've just uploaded here, in case anyone needs to view it, I just don't know why it won't work?
[url]

There are just 4 boxes to clear,
Box 1,, is cell J5 & K5
Box2 is cell D 12
Box 3 are cells G11 & H11
& Box 4 are cells M11, N11 & O11

View 9 Replies View Related

Macro To Clear Data

Jan 5, 2010

I have a spreadsheet that I enter data for cash flow purposes on a daily basis. At the beginning of each month I need to clear out the data containing values only as well as values beginnining with an = for eg 20000+50000+25000+74000 etc, but not formulas and text

I need the macro to clear the values , including data that has been added up as explaimned above from row 9 onwards and from column C

View 9 Replies View Related

Clear 1 Column Of Data

Oct 4, 2006

how do I specify in the code to clear Just column A?

View 3 Replies View Related

Transfer Data & Clear

Nov 13, 2006

I have a number of cells whereby the user has to enter data, when the user has enterd all the info I want it to be transferred to a table on another sheet (prob using a button) and the previous cells cleared ready for another new entry. I presume i will need to use a macro for this but am unsure as to how to go about it!

View 9 Replies View Related

Clear Data Only, Not Formulas

Nov 14, 2006

i need to refresh the sheet and clear all contents that are constants not as a result of formula.
i search the forum and got this code,

If ActiveCell.locked = False And Activecell.Value <> 0 Then
ActiveCell.Formula = "0"
End If

may i need to make use of loop to check whole sheet

View 9 Replies View Related

Clear Column Data With Range

Jun 15, 2014

I have

colpaste1 =Range("D2:D2")=BA
colpaste3 =Range("D4:D4")=BC

If Cell D2 = BA and D4= BC, i need VB Code to clear all contents in column BA to BC.

Note: BA and BC are the columns.

View 2 Replies View Related

Macro - Enter Data In Next Clear Row After Every Run

Jun 27, 2014

I have a macro that

- copies rows from one sheet into another sheet based on a set of criteria
- the very very very first time the macro is run, the first row of data should be put into row 7 (formatting reasons)
- however, every other time it's run it should paste into the next clear row.

But, what is happening is that, each time its run, it puts the data into row 7 => overwriting data.

I've used the offset and counter function but perhaps I need to put it in the header of the code to get it working?

My code pasted below:

[Code] .....

View 4 Replies View Related

Clear Contents Of Last Column With Data

Jan 28, 2014

I am trying to change this code to clear the contents of the last column of my "Master sheet". I am having trouble indexing to the last column, however. I found this code online but it looks much more complicated than it needs to be. Is there any way to do this easier/change this code to fit my needs?

Right now, the letter of the column I want to delete pops up in a message box, but I'm not sure where to go from there.

[Code] .....

View 2 Replies View Related

Clear Cells With Data Validation But No Value

Mar 10, 2014

In Column C, I have a variable range (usually like, C5:C100000) that has data validation in it. However, only about 10% of the cells have a value from that data validation list in them (which is what I want). I'd like to write a macro that only cleared out the cells with no value in them, so that they no longer had any data validation in them. Is this possible?

View 2 Replies View Related

Clear Data In Cells With Validation

Jul 8, 2008

I have several cells on a worksheet which contain data validation and force the user to select from a list, and several other cells with free user input. I want to use a macro to clear all data, and return the cells containing data validation to blank. (I have a blnak row at the top of all my lists, so there is a selection for "blank".)

I have no problem clearing the cells requiring user input, but my recorded macro would not select the cells with validation and return them to blank.

The first cell containing validation that I need to clear is M39. I have my code listed below for clearing the other cells. If I could get a clue on how to return M39 to blank, I could figure out the rest of the cells.

View 13 Replies View Related

Mutiple Clear Data Macro

May 16, 2009

I have made a spreadsheet but would love to be able to incorporate multiple 'Clear Data Buttons', (Macros)
that clear cell data.

I have attached several screen shots of my spreadsheet, with the URLs listed below.
(Images 1-6)
http://tinyurl.com/qba57l
http://tinyurl.com/od9upb
http://tinyurl.com/ovaeej
http://tinyurl.com/pctsvj
http://tinyurl.com/oc2qqm
http://tinyurl.com/qdhzge
Spreadsheet (xls & xlsm) in zip format
http://tinyurl.com/qczlap

It is a trading order sheet that I want to use, but also upload to a blog that I have just
started, relating to trading as a free download, hopefully it may benefit some users/visitors etc.

OK, regarding the macros.

I'm not quite sure how it is best to do it.
In total there are 13 'Clear Buttons.'

11 are 'CLEAR DATA' Buttons
12 is a 'Clear ONLY ORDER SHEET DATA' Button.
Finally 13 is a 'Clear All DATA!' Button

I think it's best if I give the cell location of where I would like the macro buttons located and what data they clear.
Clear DATA - Button 1 - (Is in Cell F3) - To Clear Data, Columns D,E,F & Rows 4 to 13)
Clear DATA - Button 2 - (Is in Cell N3) - To Clear Data, Columns G to O & Rows 4 to 13)
Clear DATA - Button 3 - (Is in Cell U3) - To Clear Data, Columns S,T & U & Rows 4 to 13)
Clear DATA - Button 4 - (Is in Cell F15) - To Clear Data, Columns D,E,F & Rows 16 to 25)
Clear DATA - Button 5 - (Is in Cell N15) - To Clear Data, Columns G To O & Rows 16 to 25)
Clear DATA - Button 6 - (Is in Cell U15) - To Clear Data, Columns S,T & U & Rows 16 to 25)................................

View 8 Replies View Related

Clear Data Not Format With Macro

Nov 7, 2009

I have a worksheet (A) that is populated from a second sheet (B). The data on A changes as necessary using another Macro. Most of the data on sheet B is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet A to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes. Here is the Macro to clear the data.

View 3 Replies View Related

Clear Data In Multiple Worksheets

Aug 29, 2008

is there a way to clear data in all worksheets according to the ranges specified in the code below, I have around 34 worksheets in which i need to clear data and i dont want to write the below code 34 times.


Sub Clear()

With Sheets("Rec")
.Range("A7:C7").Clearcontents
.Range("E7:O7").Clearcontents

End With

End Sub

View 9 Replies View Related







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