Clear Contents Of Rows To Reset Used Row Count?
Jul 18, 2013
I am working on some code that I would like the user to be able to re-run should the underlying data in the adjacent tabs change. My existing code is shown below
Code:
Option Explicit
Dim wb As Workbook
Dim ws As Worksheet
[Code]....
I'd like to be able to remove all data on the ALL tab so that when the UsedRange.Rows.Count is run again on that page it will only see the header row. In the past I've deleted or cleared data from the sheet and the Rows.Count still counts the rows which have no content in them.
View 2 Replies
ADVERTISEMENT
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
Nov 3, 2013
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?
View 4 Replies
View Related
Jun 19, 2007
How come I keep having the same value for "lastrow". eg: 35
lastrow = ActiveSheet.UsedRange.Rows.Count
It doesn't matter how many rows of data I've changed, it still show lastrow = 35.
I tried to add an extra line above but still doesn't work:
lastrow = 0
lastrow = ActiveSheet.UsedRange.Rows.Count
View 9 Replies
View Related
Jan 11, 2008
I have a userform with several combo boxes on it. I also have three command buttons, 'OK', 'Reset' and 'Cancel'. I want to code the 'Reset' command button to reset the values of the combo boxes to empty or "". I have attempted to do this simply by using the following code.
Private Sub cmdReset_Click()
cmbWaterTemp.Value = ""
cmbTypeFastIce.Value = ""
cmbIceDrift.Value = ""
cmbTrendBehavior.Value = ""
End Sub
When I click on the 'Reset' command button I get the following error.
"Run-time error '94':
Invalid use of Null"
When I use the debug tool the error pointed to is a different sub.
Private Sub cmbWaterTemp_Change()
txtTemp1Encoded.Text = Left(cmbWaterTemp.value, 1)
txtTemp2Encoded.Text = Right(cmbWaterTemp.Value, 1)
End Sub
I am not exactly sure why the error points to this sub. I know there must be a simple way to accomplish this. I am including a workbook that is similar though not exactly the same due to file constraints. What I want to happen is when the 'Reset' button is clicked is to clear all values for the combo boxes and the text boxes. I have successfully gotten the textbox values to clear although not the combo boxes.
View 4 Replies
View Related
Sep 3, 2009
I'm just finishing a project that is a collection of three calculators, each of which returns data based on a series of user-supplied variables. All the mechanics are working [thanks in part to replies to previous posts], but there is one more thing I'd like to add:
Does anyone have a way to clear the contents of another cell [actually several] cells via another cell? As the most complex calculator requires eight variables to be entered, I'd like to provide a quick way [a button?] to "clear" or "reset" the calculator - basically wipe all unlocked cells. I should note that all of the cells in question already have Data Validation of one type or another.
View 10 Replies
View Related
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
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
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
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
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
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
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
Jan 21, 2012
Macro to clear contents of entire row if any cell in Range A2:A17 is Empty.
View 9 Replies
View Related
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
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
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
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
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
Jul 18, 2006
I have a protected worksheet in which certain calculations are performed based on certain inputs.
For example, let's take Column E:
Starting in cell E4, I have cells in that column which are input cells and further down the column are calculated cells which are locked and whose formulas are hidden.
I was looking for a macro which would go down col E and clear contents of all unlocked cells without messing up any of the protected cells.
Also, there are certain cells in which I want to clear contents and some cells that I want the macro to insert a "0" value.
The input cells are non-contigous.
View 7 Replies
View Related
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
Apr 24, 2007
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
View 6 Replies
View Related
Dec 14, 2013
i have attached a template spreadesheet that 20 people uses everyday. The template works fine but what i would like to do is write a code to clear all the content that manually entered beforeclose. For some reason code is not working properly because when i wrote code to clear the contents the buttons don't work . I also attached the spreadsheet.
View 4 Replies
View Related
Apr 24, 2014
I'm playing along with Excel. I discovered it doesn't like cutting and pasting in VBA. This screws up formulas in cells.
It much prefers copying and pasting. So this involves an extra step, going back to the cells you just copied and clearing them.
So I have a selection of cells and I just copied them to a different location. Naturally the location I just copied it to then becomes selected.
I would like to go back to the cells that have the dotted selection around them and clear them. If I simply use Selection.ClearContents it will clear the currently selected cells, NOT the dotted selection.
Command to clear the contents of the dotted selection?
View 4 Replies
View Related
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
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
Oct 10, 2008
I have a dropdown list in the range E17:E91 containing a list, and two of the values in the list are "DI" and "LTC". What I need to happen is if either of these are selected, the corresponding cell in column F needs to have it's contents cleared. Ex. if in E17 the value is ever either DI or LTC, cell F17 gets contents cleared. if column E contains ANYTHING ELSE, nothing happens. That's all, I've taken care of conditional formatting and data validation already. So even if data is pasted in the worksheet into column E it needs to recognize that and always act in real time.
View 14 Replies
View Related
May 20, 2009
I have a small workbook that copies 4 cells to a database list as per the code below, 2 of the fields activate a pop up calendar for date input on a button push the data is transfered to the sheet and the cells are cleared, however on the clear contents parts at the bottom of the code it reactivated the pop up calendar,
is there a way of not having this pop up when clearing the contents.
View 4 Replies
View Related
Sep 15, 2009
This code is supposed to clear the contents of row 6 cell if the contents of row 5 cell is blank/cleared. I get an error on the "If Intersect" Line. What's wrong?
View 4 Replies
View Related
Dec 23, 2009
I'm using a macro to copy the results of a formula and paste the values only on another sheet. The result includes lots of "blank" rows. I have another macro to get rid of the empty rows and move the information up.
It's not working because the "blank" rows aren't empty. Even though I paste values only, experimentation shows that the cells that appear blank return a false to the ISBLANK test with a length of 0.
So now I think I need a macro to run after the pastespecial command to look for cells within a range with a length of zero and delete the contents of those cells, but leave alone anything with a length of >0.
I am brand new to the idea of using VBA, but I have successfully cobbled together some stuff and can usually modify things to work.
It seems I need to maybe use some sort of IF statement along with a LEN and ClearContents. I don't want to delete the blank cells, just make them truly empty so that all of my actual data stays where it should, and my delete empty rows macro works correctly.
I did a search and see some info on clearing contents of columns or rows, or clearing contents based on the content of other columns or rows, but I'm unsure of how to tell it to search each cell within a range and clear the contents of 0-length cells to make them truly empty.
View 10 Replies
View Related
