Im trying to make life easier by setting up some sort of macro function that will clear the contents of all the cells except for the ones that have formulas in them.
I don't want to delete the 1st row as it contains the titles for each column.
Macro to clear cells with numbers but no cells with formulas with in this macro:
Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub
The range is where the cells with numbers need to be cleared but not the ones with formulas.
I have a worksheet from which I regularly clear all contents by selecting all the rows and selecting 'Clear Contents' from the right button menu. However, I now have added a formula in Col Y that I want to protect. I know I could select all columns up thru X, but that would clear the R1 headers, so I want to avoid doing that. I can always work around, but I'm curious, is there a way to clear only values and thus protect the formulas when using the Clear Contents command?
is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents
from another thread (which is excellent!)
- SHG's recommendation of using a named range, for example:
Range("Inputs").ClearContents
Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents
I have a series of worksheets that are formatted for data imported from various scripts.
What I want to do is parse through each worksheet and cell, when the cell has numeric data, I want to clear the cell.
I do not want to clear the cells on the following conditions:
Cell has Text
Cell has Formula
Cell has date, month or time
I am trying to write a macro to find all cells that are returning cell errors (ie: #REF!) within a named range, to select only those cells, and then to clear the contents of only these cells. It is my understanding that replacing or altering the value is not an option because doing so will return a "" string value, instead of a clear cell. Replacing the cell's contents with a =na() is also unacceptable...these cells to be completely clear. I was unable to piece together a macro from searching around this site and thought it would be best to just ask.
View 4 Replies View Relatedi 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
i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".
the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?
I have several lists that I need to be able to print out periodically.
The main worksheet that underpins each list is over 900 lines long and numerous calcs and formulae populate columns to provide the data for my 'print lists'.
I need my 'print lists' on seperate worksheets and I have linked the data from my main worksheet results, using IF statements to stop unwanted results being displayed by making the rows appear empty (i.e. to display "").
Due to the design of my main Worksheet it is inevitable that I have many rows in each 'print list' that are not required for that particular 'print list'.
I want to remove the 'empty' rows from the print sheets at the time of printing. I cannot use the GoTo > Special > Blanks > Delete Rows because the rows are not recognised as being 'blank'.
I have attached 2 screenshots of my 'print lists'.
P.S. I am not assuming anything but I have searched on OZGRID and it appears to me that I might need to use Excel/VBA?
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
For various reasons I need to copy a the range "a2:s251" in the sheet "FWD Input" to another sheet but I need to remove any formulas.
To do this I have used the
Sheets("FWD Input").Range("A2:S251").SpecialCells(xlCellTypeFormulas).ClearContents
This works fine if there are formulas in that range, however if there aren't it gives me: " Run time error: 1004 No cells were found"
So I think I need some code that counts the number of cells in the range with formulae in them and either goes ahead if there are some or quits if there aren't.
Is there a way to clear the contents of each cell in a range without losing formulas?
View 9 Replies View Relatedi'm trying to do sounds simple enough. I have 2 Columns of data, example;
1-Apr 1423
1-Apr 1426
24-Apr1430
8-May1454
8-May1460
15-May1500
15-Jun1525
15-Jun1555
15-Jun1556
I want to clear (not delete) the cells with a duplicate date in them while keeping the cell with the number next to it. So in this case they would be A2, A5, A8, A9. I've come up with a code that looks pretty logical but it doesn't work. Here's what it looks like.
I've been pressing on with my project and figured out that I really do not know the difference between Deleting and Clear contents. I really thought they were the same but am now unsure. I guess I have 2 questions. Which is better to use? what I'm doing is removing old data and importing new data. and which is more efficient for writing code.
Sheets("Bodies"). Range("A2:Z65000").ClearContents
Sheets("bodies").Range("a2:Z65000").Delete
i need a macro that will select 0's in columns h-j.
View 7 Replies View RelatedI have a macro that I found on the net for copying a filtered selection and copying to a new sheet. I would like to alter it slightly so that it just clears the sheet called 'Interval tasks' instead of deleting it and creating a new one.
View 8 Replies View RelatedIs there a way to delete or clear a row on exit if no end date has gone into it. i.e. if the user clicks on the X button in the top right hand corner.
Every time the user clicks on start the date goes into the next empty row, if finish is clicked then the end date will go into the cell next to it. See example below. Here the user has started and ended correctly
column
AB
StartEnd
24/10/09 24/10/09
24/10/09 24/10/09
In the example below the user has forgotten to click on the finish button when they first clicked on start, they exited excel instead and no end date was inputted. When the user clicked on start again the start date went to the row below, however as there was no end date the last time, this time when they clicked on end button, then end date went to the first BLANK CELL
column
AB
StartEnd
24/10/09 24/10/09
24/10/09
I would want the delete row or clear row option, which ever would work best, to happen if the user clicked on the X in top right and corner or Exited excel via File.
Ideally I would like the row cleared and not deleted, I have read Clare Watts thread and we both seems to be having a similar problem, I have used some of the examples from her thread but non seem to work. So I now wish to go with a delete or clear row option on exit, if possible.
The row changes every time. The column for deleting or clearing would be A to AB. The end date goes in to Column B
So if the user clicked on start but did not click on the Finish Button then the last row with no end date would be deleted or cleared on exit from column A to AB
column
AB
StartEnd
24/10/09 24/10/09
24/10/09(This is deleted or Cleared of exit via X top right hand corner)
I have a spreadsheet containing 100 "requests" which can be made by a user
i would like the option for the user to be able to delete a request should they wish it either by deleting a row or clearing the contents
Currently I have a single column which is full of buttons (one for each row)
when i click the button on row 10 I would like row 10 deleted
when i click the button on row 30 i would like row 30 to be deleted etc
is there a way to do this without writing 100 macros which are specific to each line?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI would like to run a macro that would check if the date in the first cell of row is in the past.
If yes, then it would clear contents of cell in B,C,F,I,L of the same row (PLAN + SHIFTS). Or even better it would set them to 0 (zero).
Then it would hide the entire row. Is this even possible?
Please see the example: Excel.JPG
I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.
View 14 Replies View Relateddelete rows without affecting formulas
In general, when you write a formula:
In Cell A1:
delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.
Account Number
211-00-5936-00-001-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row
211-00-5936-00-002-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
I have a problem with deleting rows that contains invalid formulas, these formulas are generated randomly daily in rows that contain no data and refer to empty cells in the same row.
Is there a code to delete rows an empty row in that by data it is empty, but not so because there is a formula?
currently this code of mine doesnt work on the zero data but formular-ed cell.. cause counta on the cell gives me 1
Dim i As Long, EmptyR As Long
EmptyR = ActiveSheet.UsedRange.Rows.Count
For i = EmptyR To 1 Step -1
If Application.CountA(Cells(i, 1).EntireRow) = 0 Then
Cells(i, 1).EntireRow.Delete
End If
Next i
I have a formula in my worksheet that is copied into 7 other columns and then copied to the bottom of the worksheet. This spans about 10000 rows.
The formula is this: =IF(AC9="G",AG9,#N/A) When I import my data into the worksheet, either the formula will result in a number or will show blank with an error comment. I need to search through this range and then delete the contents of the cell (including the formula) if it is blank. For reference, the range of the columns are AJ thru AP. I hope this is specific enough, if not please let me know. I can attach the file if necessary.
If #N/A is not sufficient, then I can replace it with "x". This way if the formula does not result in a number then it will place an x in the cell. The macro would then search thru the range and delete any cell containing x.....
I have written this code to clear the contents of certain cells, lock the content of others and protect the sheet again it works on sheet1 but not on sheet 7. This is suppose to happen when the Print button on my sheet is clicked.
View 4 Replies View Relatedhow do i create a formula or macro that will: Clear the contents of Cell C1 If Both Cell A1 And B1 Are Filled. I will need to check all the cells in column A,B and C.
View 2 Replies View RelatedSub import()
On Error Resume Next
Dim wb As Workbook
Dim c As Range
Dim rngTo As Range
For Each c In Range("D1:D10").Cells
Set wb = Workbooks.Open(Filename:=c.Value)
With Workbooks("DigitalTicketMaster.xls"). Sheets("All")
Set rngTo = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
End With
wb.Sheets("Combined").Range("A2:C100").Copy rngTo
wb.Close .............................................
we have a change on our sheet, make it easy for our guys. Column A, is the date. Today 6/29.
They enter it once at the top, and it pastes for them automatically (a simple cell = $E$1)
But now, when I use the macro, it pastes over the formula that I don't want nor need, and have to call the second macro for. But then everything in that column shoes E1 on my new sheet, which is not the date... and each sheet changes dates, so I can't just have the date all the same.
What can I do in my original code to still make it loop, pull all my pages that I tell it to, but paste values only?
Would it be possible, in my loop, to highlight my cells, on that sheet... A2:C100, copy them, paste themselves right onto themselves, as values only, then copy over to my new sheet?
I want to delete names, formulas, macros of another workbook. For example, A.xls is my codes workbook. From this file, I want to delete the names, formulas, macros (but to keep the values & formatting in tact) of another workbook (there must be a prompt for which .xls file, the names etc. to be deleted). All files are in a same folder.
View 5 Replies View RelatedI am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following
View 5 Replies View Related