{"id":1355,"date":"2013-12-12T12:08:21","date_gmt":"2013-12-12T12:08:21","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=1355"},"modified":"2020-04-21T05:28:22","modified_gmt":"2020-04-21T05:28:22","slug":"excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/","title":{"rendered":"Excel  2010 VBA  Lesson 17: Excel VBA  Methods"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\">&nbsp;<strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-16-introduction-to-excel-vba-2010-object-part-1\/\">[Lesson 16]<\/a>&lt;&lt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-tutorial\/\">[Table of Contents]<\/a>&gt;&gt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-18-the-range-object\/\">[Lesson 18]<\/a><\/strong><\/h4>\n\n\n\n<h4 class=\"wp-block-heading\">17.1: Methods<\/h4>\n\n\n\n<p>An Excel &nbsp;2010 VBA object contains methods. A method usually performs certain operations. For example, ClearContents is a method that clears the contents of a cell or a range of cells.<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h4 class=\"wp-block-heading\">Example 17.1<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">\u2018Clear contents from cells A1 to A6\nPrivate Sub CommandButton1_Click()\n Range(\u201cA1:A6\u201d).ClearContents\nEnd Sub\n<\/pre>\n\n\n\n<p>For example, you can let the user select his\/her own range of cells and clear the contents by using the InputBox function, as shown in Example 17.2<\/p>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example 17.2<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\nDim, selectedRng As String\n selectedRng = InputBox(\u201cEnter your range\u201d)\n Range(selectedRng).ClearContents\nEnd Sub\n<\/pre>\n\n\n\n<p>In order to clear the contents of the entire worksheet, you can use the following code:\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>Sheet1.Cells.ClearContents<\/strong>\n<\/pre>\n\n\n\n<p>However, if you only want to clear the formats of an entire worksheet, you can use the following syntax:\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>Sheet1.Cells.ClearFormats<\/strong>\n<\/pre>\n\n\n\n<p>In order to select a range of cells, you can use the&nbsp;<strong>Select<\/strong>&nbsp;method. This method selects a range of cells specified by the Range object. The syntax is as follows:\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Range(\u201cA1:A5\u201d).Select\n<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Example 17.3<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\n Range(\u201cA1:A5\u2033).Select\nEnd Sub\n<\/pre>\n\n\n\n<p><strong>Example 17.4<\/strong><\/p>\n\n\n\n<p>The following example allows the user to specifies the range of cells to be selected.\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\nDim selectedRng As String\n selectedRng = InputBox(\u201cEnter your range\u201d)\n Range(selectedRng).Select\nEnd Sub\n<\/pre>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<p>To deselect the selected range, we can use the Clear method.\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Range(\u201cCiRj:CmRn\u201d).Clear\n<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Example 17.5<\/h4>\n\n\n\n<p>In this example, we insert two command buttons. The first button is used to select the range while the second button is to deselect the selected range.\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\n Range(\u201cA1:A5\u2033).Select\nEnd Sub\n\nPrivate Sub CommandButton2_Click()\n Range(\u201cA1:A5\u2033).Clear\nEnd Sub\n<\/pre>\n\n\n\n<p>Instead of using the Clear method, you can also use the ClearContents method.<br>\nAnother very useful method is the Autofill method. This method performs an autofill on the cells in the specified range with a series of items. The items may include numbers, days of the week, months of year and more. The syntax is\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Expression.AutoFill(Destination, Type)\n<\/pre>\n\n\n\n<p>*Expression can be an object or a variable that returns an object. Destination means the required Range object of the cells to be filled. The Destination must include the source range. Type means the type of series, such as days of the week, the month of year and more. The AutoFill type constant is something like xlFillWeekdays, XlFillDays, XlFillMonths and more.<br>\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example 17.6<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\n Range(\u201cA1\u201d)=1\n Range(\u201cA2\u201d)=2\n Range(\u201cA1:A2\u2033).AutoFill Destination:=Range(\u201cA1:A10\u2033)\nEnd Sub\n<\/pre>\n\n\n\n<p>In this example, the source range is A1 to A2. When the user clicks on the command button, the program will first fill cell A1 with 1 and cell A2 will 2, and then automatically fills the Range A1 to A10 with a series of numbers from 1 to 10.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example 17.7<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\n Cells(1, 1).Value = \u201cmonday\u201d\n Cells(2, 1).Value = \u201cTuesday\u201d\n Range(\u201cA1:A2\u2033).AutoFill Destination:=Range(\u201cA1:A10\u2033), Type:=xlFillDays\nEnd Sub\n<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Example 17.8<\/h4>\n\n\n\n<p>This example allows the user to select the range of cells to be automatically filled using the Autofill method. This can be achieved with the use of the InputBox. Since each time we want to autofill a new range, we need to clear the contents of the entire worksheet using the Sheet1.Cells.ClearContents statement.\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\nDim selectedRng As String\n Sheet1.Cells.ClearContents\n selectedRng = InputBox(\u201cEnter your range\u201d)\n Range(\u201cA1\u2033) = 1\n Range(\u201cA2\u2033) = 2\n Range(\u201cA1:A2\u2033).AutoFill Destination:=Range(selectedRng)\nEnd Sub\n<\/pre>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><br>\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">&nbsp;<strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-16-introduction-to-excel-vba-2010-object-part-1\/\">[Lesson 16]<\/a>&lt;&lt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-tutorial\/\">[Table of Contents]<\/a>&gt;&gt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-18-the-range-object\/\">[Lesson 18]<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp;[Lesson 16]&lt;&lt;[Table of Contents]&gt;&gt;[Lesson 18] 17.1: Methods An Excel &nbsp;2010 VBA object contains methods. A method usually performs certain operations. For example, ClearContents is a method that clears the contents of a cell or a range of cells. Example 17.1 \u2018Clear contents from cells A1 to A6 Private Sub CommandButton1_Click() Range(\u201cA1:A6\u201d).ClearContents End Sub For example, &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel  2010 VBA  Lesson 17: Excel VBA  Methods&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[5],"tags":[50,48,49,7],"class_list":["post-1355","page","type-page","status-publish","hentry","category-decison-making","tag-clear","tag-clearcontents","tag-clearformats","tag-method"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel 2010 VBA Lesson 17: Excel VBA Methods - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"description\" content=\"This lesson explains the concept of methods in excel 2010 vba\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel 2010 VBA Lesson 17: Excel VBA Methods - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"This lesson explains the concept of methods in excel 2010 vba\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-21T05:28:22+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/\",\"url\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/\",\"name\":\"Excel 2010 VBA Lesson 17: Excel VBA Methods - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"datePublished\":\"2013-12-12T12:08:21+00:00\",\"dateModified\":\"2020-04-21T05:28:22+00:00\",\"description\":\"This lesson explains the concept of methods in excel 2010 vba\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel 2010 VBA Lesson 17: Excel VBA Methods\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel 2010 VBA Lesson 17: Excel VBA Methods - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"This lesson explains the concept of methods in excel 2010 vba","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/","og_locale":"en_US","og_type":"article","og_title":"Excel 2010 VBA Lesson 17: Excel VBA Methods - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"This lesson explains the concept of methods in excel 2010 vba","og_url":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-21T05:28:22+00:00","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/","url":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/","name":"Excel 2010 VBA Lesson 17: Excel VBA Methods - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"datePublished":"2013-12-12T12:08:21+00:00","dateModified":"2020-04-21T05:28:22+00:00","description":"This lesson explains the concept of methods in excel 2010 vba","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-17-introduction-to-excel-vba-objects-part-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel 2010 VBA Lesson 17: Excel VBA Methods"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1355","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=1355"}],"version-history":[{"count":62,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1355\/revisions"}],"predecessor-version":[{"id":3430,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1355\/revisions\/3430"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=1355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=1355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=1355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}