Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Clear A Worksheet


right now i am clearing a worksheet via VBA with the following:
Sheet2.Cells.ClearContents

but the problem is if I had 5000 rows on that sheet, then i clear the sheet and perform an action that only displays a hundred rows.... if i then go to that sheet and press ctrl+end, it jumps to row 5000... not row 100.

so its like the original rows weren't completely cleared.

what is the vba code to totally clear a worksheet??


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Clear All Cells On Another Worksheet
I have the following code in a Excel 2003 spreadsheet with hope that I can clear contents of unlocked cells in one excel file from the code stored in another Excel file.

Sub CLEARSJCCOUNT()
'
' CLEARSJCCOUNT Macro
' Macro recorded 02/21/2008 by Steve Keene
'

Windows("copyAUTOCOUNTSHT.xls").Activate

I get the Subscript Out of Range error window when it hits the first line of code.

I've reviewed this via searching for other posts, but none seem to solve the problem directly.

View Replies!   View Related
Clear All Worksheet Cells
Gotta question about destroying the contense of a worksheet. If for instance I got a macro which uses a different source everytime I run it, and this macro gives a result in a result worksheet. How can I possibly delete the contense of the result worksheet automatically before the execution starts? What I mean is: without selecting all the cells and pressing delete or either using a for loop to go through all the worksheet cells. Is there any function which clears the contense of a worksheet? (without deleting the worksheet itself)

View Replies!   View Related
Clear Worksheet Chosen From Drop-Down
I need to create a function that by selecting a sheet name in a drop down box it will remove thinformation on the sheet from a consilidated sheet. The consolidation sheet is consolidating data from 30 sheets within the one worksheet

View Replies!   View Related
Clear And Print Tabs In A Worksheet With Checkboxes
I have a workbook with 100 tabs and I have an index page with checkboxes for each tab. I need a macro that will clear all of the checkboxes on the page with one button and a separate macro that will print only the checked tabs.

Can anyone write that code for me so I can just add it into my index tab?

View Replies!   View Related
Clear The Contents Of All The Cells In A Certain Area Of The Worksheet
I’m working on a macro to clear the contents of all the cells in a certain area of the worksheet that are not locked and do not contain a hyperlink.

(C7 is the first data-entry cell; EndPage1 is a specific cell near the bottom right of the sheet that sets the outside range of the cells to be cleared. This is the code so far:

View Replies!   View Related
Conditional Clear Based On Other Worksheet Range
I have a range on worksheet "CONTROL" from S129:S228. There are 100 rows there. I have another range on the active sheet from B17:N116, also 100 rows. If S129 has a value of 1, then I need row 17 on the active sheet to be cleared (columns B-N). And so on all the way to the bottom of the ranges. If it has no value in the cell, then no clearing takes place. So it needs to test the whole range at once. I really have no clue what the variants and ranges should be, and have NO CLUE how to define a variable range so that I can use the value that R is on in the testing cycle to tell the active sheet which row to delete.

View Replies!   View Related
Retain Formulas In A Few Cells In A One Worksheet So They Are Not Cleared By "clear Contents" Without Using Protect 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?

View Replies!   View Related
Clear Data ....
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 Replies!   View Related
Clear List Box
I am having trouble clearing a multiselect list box.

Currently have a user form with a list box that displays 200 records and allows users to select multiple entries, via checkbox, sometimes as many as 20 to 30 records. I would like to write some code that I can link to a command button that clears the list box and allows the user to start the selection process again without having to uncheck all the entries manually.

View Replies!   View Related
Clear ALL Combo Box
How do I clear all combobox at one time? Now i am clearing it one by one and i forsee that if i have alot of combobox, this list will go a long way.

View Replies!   View Related
Clear Used Range
I have a table on a worksheet and have for quite some time used a very successful macro UsedRangeClear. I found here on the forum. Anyway the macro still works on all tables on all other sheets except this one. This is the reason, somehow someone, maybe even me, copied something down or formated down to the 1,048,152th row of the worksheet. My workbook has grown from 5000 kb to 11,000 kb.

I have tried to highlight and deleted the rows, highlight and clear contents and the following macros:

Sub UsedRangeClear()
On Error Resume Next
ActiveSheet.Unprotect Password:="myfadra"
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
ActiveSheet.Protect Password:="myfadra"
End Sub

Sub ReSetTable_UsedRange()
'For ICFMR
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).row

On Error Resume Next
ActiveSheet.Unprotect Password:="myfadra"
Application.ScreenUpdating = False
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.ListObjects("Table7").Resize Range("A1:T" & LR)
ActiveSheet.Protect Password:="myfadra"
Application.ScreenUpdating = True
End Sub

Each option freezes Excel. I am sure there is something easy I am just missing. Soooo, Please offer any suggestions you can think of.

A Loyal Mr. Excel Follower
Alecia

View Replies!   View Related
Clear #VALUE! From Cells?
I am getting a #VALUE! error in one of my cells. I know it is because my worksheet is missing information to complete the formula. The information will be inputted periodically in the worksheet (lets call it worksheet number one). But, I have the results of cells in worksheet one that are tied to worksheet number two. Worksheet number two is getting the #VALUE! also until worksheet number one is completed (periodically).

But, in worksheet number two, I have an auto sum that adds up the results from worksheet one so I am getting the same #VALUE! error on the auto sum because only a few worksheet from worksheet one are complete. Is there anyway to clear the #VALUE! and get a number in the auto sum even if only a few worksheets from worksheet number one are inputted?

View Replies!   View Related
VBA Clear Contents
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 Replies!   View Related
Clear Cells Using VBA
I need to clear all cells in a column that have a particular value. The value is sometimes a constant like "0" (zero) and sometimes is a value based on a formula. Is there a VBA code that will allow me to select those cells in column matching a particular value and clear them?

View Replies!   View Related
Clear The Clipboard
I'm trying to clear the clicpboard using VBA so i don't get messages asking me what to do. is this possible and if so how? i could simply turn all alerts off but i rather not.

View Replies!   View Related
Clear Value From Cell
I have written a vb code to import data from notepad to excel.
I have created a command button to clear those data after excecuting certain function.
For eg. if i have data in column A,B & C of excel i want a code which will clear the value in B & C only starting from a specific row of that column.

View Replies!   View Related
Clear Sheet
How can I clear all elements in a sheet, including charts with vba code?

or

How can I select all chart in a sheet and then delete them?

View Replies!   View Related
Print And Clear
I HAVE JUST PUT THIS CODE TOGETHER

Private Sub printcmd_Click()
Dim lastrow As Integer

Sheets("A").PrintOut

'THEN CLEAR

Sheets("A"). Range("A5:K65536").ClearContents

End Sub

BUT MY PRINTER HAS BROKE AND DONT GET A NEW ONE TILL NEXT WEEK
AND WAS WONDERING IF THIS WOULD PRINT AND THEN CLEAR WHAT WAS ON THE SHEET OR NOT

View Replies!   View Related
Clear Clipboard
I'm currently opening up worksheets, copying the data there inside tables and pasting it over to a new worksheet which is basically the summary of all the data, unfortunately because I am moving large amounts of data, half way through the process I get a pop up asking me if I want to copy data to wherever to make sure it's stored or do I want to loose it, of course it's only a matter of hitting the no button to re-write the data so copy and paste carries on working but is there no function in VBA to clear the clipboard after every copy and paste, such as clipboard.clear, or something along those lines? Anything that will stop this message from appearing, I was my summary worksheet to work without popups appearing because then that would mean someone would have to sit there as it copies and pastes values, clicking no every time the clipboard gets full.

View Replies!   View Related
Macro To Clear Contents.
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 Replies!   View Related
Clear Cell Value Without Format
I need help with the vba function that will delete the cell value but leave colors if any ...

View Replies!   View Related
Vba Clear Contents Of Column
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 Replies!   View Related
Clear Data Only, Not Formulas
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 Replies!   View Related
Clear Row Based On Checkbox
I have a worksheet with several rows (A3:H70). Column J contains checkboxes (1 in each row). I'm trying to develop a macro that will clear rows based on the value of the check boxes. For example, if the checkbox on J3 is checked, that row (A3:H3) will be cleared.

If the checkbox on J4 is UNchecked, then that row will be skipped and so on.

View Replies!   View Related
Clear Cells To Right IF Macro
if I had text in column A, I would like to clear all the cells to the right and leave the text in column A.

Also, if I had text in column A and B, I would like to clear the cells to the right.


View Replies!   View Related
Clear All Data Macros
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 Replies!   View Related
Clear Named Ranges
There are over 100 named ranges in my excel 2007 workbook. Is there an easy way to clear all of these out?

View Replies!   View Related
Clear Non-blank Cells
The best script to clear all non-blank cells, in my case from Cells(7,1) forward. I currently use this:

.Range(Cells(7, Columns.Count), Cells(Rows.Count, 1)).Value = ""

Will this scipt take a lot of memory to execute? Is there any other method?


View Replies!   View Related
Reset Memory Or Clear Old Value
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 Replies!   View Related
Macro To Clear Data
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 Replies!   View Related
Clear A Formatted Cell
I have a cell that I've formatted to dd/mmm/yy, It used to work, but now it doesn't. When I type in 1/2/03, the formatted cell shows 0-Jan-00, The formula bar shows =1/1/2. I seem to have a formula in the cell, but I can't get rid of it. I know it's most probably somethinmg I've done , but I don't know what. I've tried everything I know. I can't seem to re format the cell , and I can't delete the formatting in it. I'm stuffed.

View Replies!   View Related
Clear All Controls On A Form
Is there a short way to clear text boxes, check boxes and option buttons in one command. I dont want to delete them I want them to be able to accept more data once the operator has finished


View Replies!   View Related
Clear Selection Areas
I frequently receive workbooks containing up to 50 sheets and on most of them there is a selected area.

Is it possible to put a bit a code in the worbook when opening to clear all selected areas on all sheets.

View Replies!   View Related
Clear Data Macro
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 Replies!   View Related
Transfer Data & Clear
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 Replies!   View Related
Automatically Clear The Formatting
When doing vlookups, I frequently encounter an issue where one column isn't formatted the same and the vlookup won't work.

As an example I am trying to do a vlookup from a sql report downloaded to excel and the formatting on the column isn't right. If I type over the data, the vlookup works.

Is there a way to automatically clear the formatting so the vlookup will work.


View Replies!   View Related
Macro To Clear Cells
I have data in columns a,b,c and d from row 1 to about 200 right now. Every week another 9 rows are added. I wanted to create a macro that will delete the last 9 entries in each column.


View Replies!   View Related
Clear Range From All Worksheets
I have a macro to clear all contents for a sheet in a workbook. The problem is that every file has over 20 sheets of data. Is there a way I can code a for statement to clear all sheets in this one workbook? Is it possible to make a for statement for multiple workbooks with several sheets?


Range("A11:Z11").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.ClearComments

View Replies!   View Related
Speed Up Frame1.Clear
I have a Frame on a UserForm. The Width of the frame depends on values given in TextBox.Dat1 and TextBox.Dat2.

Now I tried it with a width of 2200 with a scroll bar, but when I change the value in the Textboxes I need the Frame to be cleared. Now this is my question. I use "UserForm1.Frame1.Clear" but this can take up to 1 minute.

Is there anyway to speed this up?

View Replies!   View Related
Err.Clear V On Error Goto 0
I am selecting cells from a range by using "SpecialCells" and need to determine whether this produces an error (as it does in the event that there are no cells that fit the criteria) so I write
On Error Resume Next
Then I check the error number and if it is not 0 the next line of code is skipped.

Within the same procedure I do this again, but on a different range and need to check the error number again.
If no error is found, the previous error number will be kept so before running the second piece of code, I add the line
Err.Clear
Is their any difference between this approach or using the line
On Error Goto 0

View Replies!   View Related
Clear Contents If 2 Characters Or Less
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 Replies!   View Related
Clear (not Delete) Duplicates
i'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.

View Replies!   View Related
Clear Command Button
I want to create a command button that will clear whatever is written in a text box. I guess for this to work it needs to select all, then clear. The command button and the text box have been created using the control toolbox.

View Replies!   View Related
Clear Autofilter Range
I'm wrinting a macro to copy specific data from a table. To do that I'm using a Autofilter and a list of criteria. The macros works fine for existent data, I'm mean, when the result of the filter is not null, but when the filter doesn't find a result, instead of clearing the filter range, it keeps the last valid. The result is a colapse and freeze the Excel. I'd like to know how can I reset or clear the filter range?
The code I'm using is:


Sub Filter_01()

'Definições preliminares
Dim rng As Range
Dim rng2 As Range
Dim ARLE
Dim filter_valid, filter_invalid As Variant

filter_valid = Array()
filter_invalid = Array()
'Criação do Arquivo de Destino
Caminho = "D:Documents and Settingscjcs.ABMeus documentosAutomaSIPPPlanilhas"
Nome_Arquivo = "Produtos_Tanques.xls"
Nome_Completo = Caminho & "" & Nome_Arquivo ...

View Replies!   View Related
How To Clear Userform Memory
I have 2 userforms (userform1 and userform2)

userform1 has a combobox1 (list of customers) with an commandbutton1
userform2 has many textboxes

when I click on the userform1.commandbutton1 - userform2 shows with textbox1.text being what was in userform1.combobox1

when i am done with userform2 and click close to go back to userform1, chose a different customer and click the commandbutton1, userform2 opens with the previous value.

how can i clear the previous?

currently when i close userform2 with commandbutton1 i use the following code


Private Sub CommandButton1_Click()

unload userform2
set userform2 = nothing

End Sub

View Replies!   View Related
Clear Duplicate Entries
I am trying to find a macro that will clear duplicate entries in a column of data, and then move to the next column and repeat the process until it gets to a blank column.

There are blank cells between entries in a column. I need to keep the data unsorted as the cells are linked to others in the row, therefore I dont want to delete the cells, just clear the contents.

View Replies!   View Related
Clear Cells With Specified Style
I have a spreadsheet wherein the defined data input cells are formatted using styles, i.e. data input style. I would like to know how to select all cells formatted with that style within a spreadsheet (whether worksheets are visible, hidden or very hidden) and delete the contents of those cells while still keeping the cell style.

View Replies!   View Related
Macro For Min Value Then Clear Contents
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 Replies!   View Related
Clear Object In Sheet
I have many objects in a sheet, for example Picture(from file), Oval, command button... I want to clear these objects. How can I do ?

View Replies!   View Related
Clear Variable Range
why the first version below works but the second doesn't

1st

ThisWorkbook.Worksheets("GBP%"). Range(Cells(8, c), Cells(z, c)).ClearContents

2nd

Worksheets("GBP%").Range(Cells(8, c), Cells(z, c)).ClearContents

c = 16 (uses a for statement to go through columns 16 to 111)
z = in this case 24, but is dynamic depending on number of files in a list

Currently using Windows 2000 with office 2000

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved