Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel clearcontents

Using VBA ClearContents to Clear Cells in Excel

The VBA Tutorials Blog

The longer you’ve been programming, the more tasks you’ll automate. You’ll likely encounter proccesses you want to automate that have slight differences, and to simplify both as much as possible, you’ll build two separate functions or subs.

For example, you might notice that Process A is better when Variable X is required, while Process B is better when Variable X is optional. In this scenario, you end up with Sub A and Sub B, which you can access independently.

This is often how similar built-in functions arise. In our case, we’ll look at the difference between VBA ClearContents and the VBA Clear methods as part of a Range in Excel. Their names imply similarity, and they do both clear information from cells. But important differences exist.

VBA Clear Method

The VBA Clear method is associated with a Range object in Excel. A Range is just a group of cells. Let’s say you have a table of information for a community center that looks like this:

Here, we have formulas in the Time Out column and the More Members cell, plus conditional formatting for the Member , Return Time , and Time Out columns. Moreover, we’ve used date formatting for the Date column, which can automatically infer the day of the week for us. We might even consider adding data validation for the Check Out Time so users can’t enter invalid times, like before the community center opens.

Perhaps you trust your visitors to fill in the correct information, and you simply look at the table every weekend. Or if you are very ambitious, maybe you set up a spiffy customer-facing page complete with appealing graphics and receive data in a userform, then look at the results once a week.

With a few visitors it would be trivial to select the table with your mouse and reset it. But what if you have hundreds of visitors? You probably won’t want to scroll down to find the bottom then highlight the whole table and delete it. Moreover, if you just select and press Del , you’ll lose formulas. In our case, you’d need at least two selections to avoid removing the Time Out column.

For that reason, you might want to simply implement a button to clear everything with this code:

This will preserve your formulas in the Time Out column (the 5th column).

Unfortunately, the .Clear method has removed all of our formatting in the cleared ranges:

  • Return times past 5:30pm are no longer conditionally highlighted, nor are members conditionally marked in green.
  • Our date formatting is gone. If someone enters a date in the usual format, such as 09/15/2019, they won’t see the commas or day of the week.
  • The cell borders and fill colors have vanished.
  • All text and cell formatting preferences, like bold, font size, and shading are cleared.
  • If we had any data validation rules, such as no Check Out Times before 9am, these too would be removed.

The VBA Clear method resets your cells to the “fundamental form” you see when you first open a new workbook. The only exception is cell sizing, which remains. If we just want to remove contents but retain formatting, we can use Clear’s close cousin, ClearContents .

VBA ClearContents Method

After repopulating our table to the original we had above, let’s try to clear the data but retain the formatting we worked so hard to create. This can be accomplished with the VBA ClearContents method. Instead of clearing everything, ClearContents just clears the data in the cells. The numbers, text, dates, and times will be removed, but all formatting is retained. “Contents” simply refers to the information users usually enter in a cell.

This second code block is much nicer. We don’t have to worry about setting up cell, text, date, number, or border formatting again, and we retain all our rules for conditional formatting and data validation.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, arrays, strings and more — grab a copy below.

You could assign this macro to a button or a shape directly on your Excel sheet for easy clearing. But can we make the code any prettier? Answer: yes.

Clear Contents while Retaining Formulas

While the person clearing the table may only need one step (pressing the “Clear” button you add to the sheet), the code is still two steps: clear contents to the left of the Time Out column and clear contents to the right of the Time Out column. By default, the VBA ClearContents method would remove formulas entered in a cell so we skipped the Time Out column in our example.

Fortunately, there is a way to clear certain types of data in cells. All data input by users will be constant, because it does not change unless a user manually changes it. This contrasts with formula-based cells, which are recalculated whenever the worksheet changes. Type markers are visible to the VBA engine, and you can single them out by invoking the SpecialCells property of a Range object:

This single line of code identifies all constant cells from column 1 to column 7, starting from the 4th row, and clears their contents. Because we told the macro to only clear xlCellTypeConstants in our range, any formulas will not be cleared and, since we used .ClearContents instead of .Clear , all cell formatting is retained.

If you have Intellisense turned on, you’ll be able to see the other types of special cells, too. If you made mistakes entering your formulas but really want to retain the constant data, you could even remove only the formulas by choosing xlCellTypeFormulas .

An Alternate «Clearing» Method

There is another method to “clear” data. This is not a true clearing operation but really an overwriting operation.

You can set the range of cells you want to clear to an empty string:

This accomplishes the same result as .ClearContents . It preserves data validation, formatting, and preferences. You can even use it with Special Cells to match the behavior of our second ClearContents example:

This example clears the contents from all cells except those containing formulas. In other words, it clears the constants.

Читать еще:  Перестал открываться файл excel

If you want to make your code more readable, you can set the cells equal to vbNullString instead of a literal empty string.

This may make your intentions easier to understsand, especially for a future debugger who may mistake the empty string assignment for a code bug. If you explicitly write out that the assignment is for a null string, it will be less confusing to him/her.

In this tutorial, we explained two similar built-in VBA methods for removing data from ranges in Excel. These methods are .Clear and .ClearContents . The Clear method resets your cells to the fundamental form with no text or cell formatting and no rules for data validation or conditional formatting, while the ClearContents method clears the contents of the cells but retains your formatting and any data validation rules.

This tutorial also showed you another way to “clear” contents, which involves overwriting the data already there with an empty string. This method results in the same outcome as .ClearContents on a superficial level (i.e., what you can see on the spreadsheet).

You can also single out specific types of cells for all three methods with the Range.SpecialCells property to avoid erasing certain kinds of data.

If you liked this tutorial, we have a 20+ part free email series designed to help you learn more VBA tricks like this one. Check it out using the form below and share this article on Twitter and Facebook.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

This article was written by Cory Sarver, a contributing writer for The VBA Tutorials Blog.

VBA Clear Contents

Excel VBA Clear Contents

Clear contents is a method in VBA which is used to delete or remove the values which are stored in the cells provided to it, this method makes the cell range empty and it is used with the range property to access the specified cell range, the example to use this method is as follows range(“A1:B2”).ClearContents this method will clear the contents of cells from A1 to B2.

In excel adding the data and deleting the data is a common routine task. Sometimes we delete single cell value, sometimes many cells values and sometime we may require to delete the entire worksheet content as well. In this article, we will show you how to use the “Clear Contents” method in Excel VBA. In VBA we have many methods to do this like “Clear”, “Delete”, and “Clear Contents”.

What is Clear Contents in Excel VBA?

Before I tell you about Clear Contents in VBA let me show how we can delete or clear off the data in the specific range.

For an example look at the below data.

Now if I want to clear off from the cell A1 to C3 we need to first mention the range of cells using VBA RANGE object.

Code:

After mentioning the range of cells by using RANGE object we need to select the method “Clear” to clear off the mention the cell values.

Code:

This will clear off the mentioned cell values.

Code:

Apart from the clear method, we can also use “DELETE” method as well.

Code:

This will delete the mentioned cell values just like our clear method has done.

If you want to delete all the cells data then you can use VBA CELLS property with worksheet name.

Both the above codes will delete the entire data of the worksheet “Sheet1”. It will delete the cell values right from the first cell to the last cell of the worksheet.

If you want to delete the present sheet cells then you can use Active Sheet object.

Difference Between Clear & Delete Methods

I know this question should have already played in your mind.

Yes, there is a difference between these two methods.

When you use the method “Delete” it will delete the cell and the below cell will take over the position of the deleted cell.

For an example look at the below image.

Now I will use the delete method to delete the cell A1.

Code:

I will run this code and see what happens.

Look what happened here as I told when I deleted the cell A1 it is deleted but the cell A2 moves one cell up and occupied the deleted cell. This will lead to data mismatch. So be careful while using the Delete method.

Now for the same data, I will clear method.

Code:

Now see what happens when I run this code.

This code has just vacated the cell A1 without altering other cells. This looks proper method to delete only the part of the cells of the entire data range.

Use VBA Clear Contents Method to Retain Formatting of Cells

If you have observed the previous two methods, those two methods not only deleted or cleared off the cells provided. It also deleted the formatting of the cells we have provided.

In order to retain the formatting of the cells we need not use neither “Delete” nor “Clear” but we need to use the VBA “Clear Contents” method.

The moment you enter the range of cells by using RANGE object it will show all the properties and method associated with it.

We can access “Delete”, we can access “Clear”, and we can also “ClearContents” methods.

Select this method.

Code:

Now, this will clear content from A1 to C3 cell but we will have all the existing formatting as it is.

As you can see in the above picture we have cell color in VBA, borders and every formatting associated with those mentioned cells.

Similarly, we can clear the contents of other sheets as well.

This will clear the contents from the cells A1 to D10 in the sheet “Sheet1”.

Similarly, we can delete the other opened workbook cells as well.

Читать еще:  Excel vba формула в ячейке

Loop Through all the Worksheets and Clear Contents of Specific Range

Assume you have many sheets in your workbook and you want to delete the range of cells from A1 to C15 in all the sheets we need to use For Each Loop in VBA to do this.

Below code will do the job.

Code:

Just in case if you want to clear off the entire worksheet data then you need to use below code.

Code:

You can download this VBA Clear Contents Excel template here – VBA Clear Contents Template

Recommended Articles

This has been a guide to VBA Clear Contents. Here we learn how to use Clear, Delete and ClearContents method in VBA to clear data in Excel along with some simple to advanced examples . Below are some useful excel articles related to VBA –

VBA set cell value after .ClearContents

Why does the cell value not set in another function when clearing the contents of the range in another function?

I’m trying to set a cell value to «All» after clearing the cells in the range. I’ve even tried to get a message box to pop up to see if i can somehow check if my check value is correct.

DelRange is the range i’m clearing.

Building is the cell that i’m checking the value for and if it’s blank, it needs to change to «All».

clearPreviw is used to clear another sheet, which it’s doing.

I’ve run this test as a separate process which works, but once again when running it as a call function right after .ClearContents seems to stop this.

I’ve been poking at it and searching but i can’t wrap my head around this.

I can’t tell from what you posted what the actual problem is. ‘ClearSheet’ doesn’t do anything to the ‘Building’ range. – Rory 01 сен. 17 2017-09-01 06:44:23

It is very unclear what your actual question is because you didn’t ask one! Make sure you ask something other than “*Can someone help me?*” (see [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613)) Also unclear «*but doing it with ClearSheet doesn’t seem to fire the function.*» (1) which function? (2) where in the code? (3) which is the procedure you initially called? Can you provide a [mcve]? – Pᴇʜ 01 сен. 17 2017-09-01 06:47:26

@Rory Thanks for the feedback, ‘ClearSheet’ is being used to clear the range of cells that include the cell from the range ‘SelBuild’. My problem is that after clearing the range that includes the Cell that i need to change to «All», i cannot apply the value «All» to the cell. – Jakes Van Niekerk 01 сен. 17 2017-09-01 07:30:39

@Peh, Thanks for your comment. I’m not sure how to ask the question otherwise. Why can’t i apply a value to a cell after clearing the contents? I’ve tried setting ‘Building.Value = «All»‘ with the ‘Clearsheet’ function but it seems to be doing nothing. – Jakes Van Niekerk 01 сен. 17 2017-09-01 07:35:34

@JakesVanNiekerk You need to be very precise in explaining what you are doing. There is no ‘Building.Value = «All»‘ in the procedure ‘Clearsheet’. Nor did you answer any of my 3 questions. So how can you expect a good answer if you don’t provide the information. Help us help you. We need **exactly** the code you are running. And answer the questions as precisely as you can. You can [edit] your question to add something. – Pᴇʜ 01 сен. 17 2017-09-01 08:22:25

@Peh, Thanks i’ve edited the question. Let me desensitize the info on the sheet and share the workbook. I’ve just thought of something now. I have the sheet checking for changes on that same range and then applying filters according to the cell values in that range. Would i need to stop the sheet change until ‘.ClearContents’ is done and the new value has been applied to the ‘Building’ range? – Jakes Van Niekerk 01 сен. 17 2017-09-01 08:59:10

@JakesVanNiekerk you can easily test this by debugging the code step-by-step using F8. Go through your code and see what happens in every single step. That’s a common way to find bugs in your code and you should be familiar with that when you write own code. – Pᴇʜ 01 сен. 17 2017-09-01 09:13:00

Is SelBuild a single cell? If not I would advise using a for each cell in range loop to set the values to «All» if they are blank. If it is a single static cell, try referencing the cell directly perhaps – Richard Tompsett 01 сен. 17 2017-09-01 09:18:54

@Glitch_Doctor doesn’t matter if it is a range or a single cell. Even ‘Range(«A1:A5»).Value = «All»‘ would fill in ‘All’ into all 5 cells of that range. – Pᴇʜ 01 сен. 17 2017-09-01 09:25:12

@Peh True but if any one of those cells contained a value, the IF statement wouldn’t trigger, right? – Richard Tompsett 01 сен. 17 2017-09-01 09:35:36

@Glitch_Doctor no the ‘IF’ statement would cause a *run time error 13* if the range is not a single cell: ‘If Range(«A1:A5»).Value = «» Then ‘=> error 13’ – Pᴇʜ 01 сен. 17 2017-09-01 09:41:42

If you are doing this from a Change event, you need to disable events while it’s running as it will trigger further change events otherwise. – Rory 01 сен. 17 2017-09-01 11:19:58

@Rory you are 100% correct. After removing the Change event, it worked. If i understand the issue correctly, please correct me if i’m wrong, it seems like as soon as the range is cleared, the Change event triggers and stops the ‘*.Value’ from running. For now for the purpose of sending in the dashboard to the client for presentation, I’ve created an «All» button to apply only the value and not run ‘.ClearContents’. I’ll fix it in the week and post the solution. – Jakes Van Niekerk 02 сен. 17 2017-09-02 10:59:46

excel vba — selection.clearcontents and how to reverse

chief

I am trying to devise a code that will allow me to show only particula
columns in a spreadsheet, or that will hide particular columns in
spreadsheet. I have set up a password form and i have a question.
When I run this and the person puts the name in as «john» and th
selection clears its contents, what code can i use so that when someon
enters the name «joe» then the values that were in those cells wil
return to their original selves. I guess sort of like a temporar
hiding of the values?

Читать еще:  Работа с vba в excel 2020

Private Sub CommandButton1_Click()
If TextBox1.Text = «john» Then
UserForm1.Hide
Sheet1.Range(«C3113″).Select
Selection.ClearContent

Advertisements

Jim Rech

When one of the «Frank»‘s doesn’t answer your question in 10 minutes it’s
most likely because it’s not clear. This isn’t clear. I’m going to venture
a guess. If the user does not enter «john» in the text box and you want to
restore default values, I think your best bet may be to copy them in from
some out-of-sight holding area.

Btw, there is no reason to select a range to clear it. This is all you
need:

Sheet1.Range(«C3113″).ClearContents


Jim Rech
Excel MVP

|I am trying to devise a code that will allow me to show only particular
| columns in a spreadsheet, or that will hide particular columns in a
| spreadsheet. I have set up a password form and i have a question.
| When I run this and the person puts the name in as «john» and the
| selection clears its contents, what code can i use so that when someone
| enters the name «joe» then the values that were in those cells will
| return to their original selves. I guess sort of like a temporary
| hiding of the values?
|
| Private Sub CommandButton1_Click()
| If TextBox1.Text = «john» Then
| UserForm1.Hide
| Sheet1.Range(«C3113″).Select
| Selection.ClearContents
|
|
| —
| Message posted
|

Bob Phillips

Use a hidden sheet and save them off there

Private Sub CommandButton1_Click()
If TextBox1.Text = «john» Then
UserForm1.Hide
with
Sheet1.Range(«C3113″).
.copy destination:=hidden.Range(«C3113″)
.ClearContents
End With
ElseIf TextBox1.Text = «joe»Then
hidden.Range(«C3113″)..copy _
destination:=Sheet1.Range(«C3113″)
‘etc.

Bob Phillips
. looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips

AFAIK there is only one Frank

Jim Rech

AFAIK there is only one Frank

Hmmm. Did you know that Kabel is German for Cabal? Look it up.

Frank Kabel

Just to give the correct translation for my name
Kabel = cable
nothing to do with to cabal = to intrigue

Loop clearcontents

Hi All,
I am having trouble looping clearContents method.

I am aiming to clear horizontal ranges of cells ( E6:L6&N6) to which I name «ToClear» if conditions are met. I have managed to clear contents but I have until row 81 to clear. Could you please assist on this?

  1. Sub find_Week_Batch_GreaterDay()
  2. Dim find_Week_Batch_GreaterDay As Integer
  3. Dim ClearVal As String
  4. ClearVal = Sheets(ActiveSheet.Index — 1).Range(«F6»).Value
  5. If Clearval Like «*WEEK*» Or _
  6. Clearvall Like «*BATCH*» Then
  7. If Sheets(ActiveSheet.Index — 1).Range(«O6») > 1.1 Then
  8. ActiveSheet.Range(«ToClear»).Value = Sheets(ActiveSheet.Index — 1).Range(«ToClear»)
  9. Else
  10. ActiveSheet.Range(«ToClear»).ClearContents
  11. MsgBox «Please Put New Work Order»

Re: Loop clearcontents

Can’t say what exactly you are doing without the sample workbook, but I am assuming this is what you are looking for

  1. Sub find_Week_Batch_GreaterDay()
  2. Dim ClearVal As String
  3. Dim lngLoop As Long
  4. Dim strRowsToPutNewWorkOrder As String
  5. For lngLoop = 6 To 81
  6. ClearVal = Sheets(ActiveSheet.Index — 1).Range(«F» & lngLoop).Value
  7. If ClearVal Like «*WEEK*» Or Clearvall Like «*BATCH*» Then
  8. If Sheets(ActiveSheet.Index — 1).Range(«O» & lngLoop) > 1.1 Then
  9. ActiveSheet.Range(«E» & lngLoop & «:L» & lngLoop).Value = Sheets(ActiveSheet.Index — 1).Range(«E» & lngLoop & «:L» & lngLoop).Value
  10. ActiveSheet.Range(«N» & lngLoop).Value = Sheets(ActiveSheet.Index — 1).Range(«N» & lngLoop)
  11. Else
  12. ActiveSheet.Range(«E» & lngLoop & «:L» & lngLoop).ClearContents
  13. ActiveSheet.Range(«N» & lngLoop).ClearContents
  14. strRowsToPutNewWorkOrder = strRowsToPutNewWorkOrder & lngLoop & «, «
  15. End If
  16. End If
  17. Next lngLoop
  18. If strRowsToPutNewWorkOrder <> «» Then
  19. MsgBox «Please Put New Work Order In Rows » & Left(strRowsToPutNewWorkOrder, Len(strRowsToPutNewWorkOrder) — 2)
  20. End If
  21. End Sub

Re: Loop clearcontents

Try changing the «Refers To» for the «ToClear» Named Range to

Change Sheet1 to whatever your sheet is named

We now have a reputation system in place. If my reply helped please «Like» the reply by clicking the «Like» icon at bottom right of my reply.

Re: Loop clearcontents

Try changing the «Refers To» for the «ToClear» Named Range to

Change Sheet1 to whatever your sheet is named

Thought OP wanted to check a criteria before clearing a row

  1. [CODE][COLOR=blue]If[/COLOR][COLOR=#333333] Sheets(ActiveSheet.Index — 1).Range(«O6») > 1.1 [/COLOR][COLOR=blue]Then[/COLOR][COLOR=#333333] [/COLOR]

Re: Loop clearcontents

You could be right, as I saw it the OP needed to clear the contents of 81 rows if F6 was equal to the ClearVal variable and O6 was greater than 1.1.

It is not specified if those conditions applied to columns F & O of all 81 rows or if it is just a one off condition for F6 & O6.

We now have a reputation system in place. If my reply helped please «Like» the reply by clicking the «Like» icon at bottom right of my reply.

Re: Loop clearcontents

You could be right, as I saw it the OP needed to clear the contents of 81 rows if F6 was equal to the ClearVal variable and O6 was greater than 1.1.

It is not specified if those conditions applied to columns F & O of all 81 rows or if it is just a one off condition for F6 & O6.

Hi SMC and KjBox,
I think I made a mistake when I used «OR» and «AND» with my previous «clearcontents».

my intents were:
If F6 string of text has either Week or Batch (case insensitive) and o6 is greater than1.1 then those should stay in the sheet in the same location (range).
clear contents to all rows of F6 to L6 and N6 (skipping M6) then test the following rows down until 81st rows and clear the contents as well if those mentioned conditions are met.
finally msgbox should put new Work Order to those cells which are just content cleared.

appreciate your reply help on this.

Re: Loop clearcontents

In order to clear up any further misunderstanding could you attach your workbook, and show expected result on a separate sheet.
[sw]*[/sw]

We now have a reputation system in place. If my reply helped please «Like» the reply by clicking the «Like» icon at bottom right of my reply.

Re: Loop clearcontents

Hi Kjbox.
Apologies for responding late.
I figured it out thru navigating online help and made some adjustment to SMC’s suggestion. thanks much.

However this takes more than a minute to complete running and I am wondering if I could make it faster. Please see below.
Sub Vba_for_clear_contents()
Dim ClearValue As String
Dim LongLoop As Long
Dim refValue As Variant
Dim ClearValueEL As Variant
Dim ClearValueN As Long
Dim ClearValueO As Double
Application ScreenUpdating = False

For LongLoop = 6 To 81
refValue = Sheets(ActiveSheet.Index — 1).Range(«F» & LongLoop).Value
ClearValueEL = Sheets(ActiveSheet.Index — 1).Range(«E» & LongLoop & «:L» & LongLoop).Value

Clear_valueN = Sheets(ActiveSheet.Index — 1).Range(«N» & LongLoop).Value

Clear_valueO = Sheets(ActiveSheet.Index — 1).Range(«O» & LongLoop).Value

If (refValue Like «*week*») Or (refValue Like «*Batch*») Or (ClearValueO > 1.1) Then

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×