{"id":2211,"date":"2023-09-01T07:29:00","date_gmt":"2023-09-01T07:29:00","guid":{"rendered":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/"},"modified":"2023-12-06T05:48:44","modified_gmt":"2023-12-06T05:48:44","slug":"speciale-cellen","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/","title":{"rendered":"Hoe gebruik je de speciale celmethode?"},"content":{"rendered":"<div class=\"excel-voor-de-inhoud\" id=\"excel-826758045\"><script type=\"text\/javascript\">\r\n\tatOptions = {\r\n\t\t'key' : 'c1158f160081d6540a8409e6925dab94',\r\n\t\t'format' : 'iframe',\r\n\t\t'height' : 250,\r\n\t\t'width' : 300,\r\n\t\t'params' : {}\r\n\t};\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/www.highperformanceformat.com\/c1158f160081d6540a8409e6925dab94\/invoke.js\"><\/script><\/div> <p>In VBA kunt u met de speciale cellenmethode een cel of celbereik van een specifiek type en een specifiek waardetype selecteren. Stel dat u wilt weten welke cellen in het bereik A1:A10 een formule hebben en als waarde zijn genummerd? Welnu, u kunt dit resultaat bereiken met de SpecialCells-methode.<\/p><h2 class=\"wp-block-heading\"> Gebruik de speciale cellenmethode in VBA <\/h2><div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-54756\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png\" sizes=\"auto, \" srcset=\"\" alt=\"gebruik de speciale cellenmethode in vba\" width=\"614\" height=\"275\"><\/figure><\/div><p> Hier schrijven we code om het adres van de laatst gebruikte cel in het bereik A1:A10 te achterhalen. Merk op dat er in de speciale celmethode twee argumenten zijn, waarvan er \u00e9\u00e9n vereist is en de andere optioneel.<\/p><ol><li> Declareer eerst een variabele als een bereik om dit celadres op te slaan dat door de code wordt geretourneerd. <br><img loading=\"lazy\" decoding=\"async\" title=\"2-declareer-een-variabele-als-bereik\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/2-declarer-une-variable-comme-plage.png\" sizes=\"auto, \" srcset=\"\" alt=\"declare a variable as range\" width=\"328\" height=\"284\"><\/li><li> Raadpleeg daarna het bereik en gebruik de methode &#8220;SpecialCells&#8221;, waarbij u het argument &#8220;Type&#8221; moet opgeven met &#8220;xlCellTypeLastCell&#8221;.<\/li><li> Stel vervolgens deze speciale regel celcode in op de variabele die u in de eerste stap hebt gedefinieerd. <br><img loading=\"lazy\" decoding=\"async\" title=\"3-gebruik-de-speciale-cel-methode\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/3-utiliser-la-methode-des-cellules-speciales.png\" sizes=\"auto, \" srcset=\"\" alt=\"use the specialcells method\" width=\"462\" height=\"209\"><\/li><li> Vanaf hier moet u een VBA-berichtenvenster gebruiken om het adres van de celretouren op te halen via de speciale celmethode.<\/li><li> Gebruik aan het einde de eigenschap adres met de variabele om het adres van de laatste type cel op te halen. <br><img loading=\"lazy\" decoding=\"async\" title=\"4-Vba-berichtenbox-om-het-adres op te halen\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/Boite-de-message-4-vba-pour-obtenir-ladresse.png\" sizes=\"auto, \" srcset=\"\" alt=\"Vba message box to get the address\" width=\"449\" height=\"203\"><\/li><\/ol><p> Hier is de volledige code.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Dim myRng As Range Set myRng = Range(\"A1:A10\").SpecialCells(xlCellTypeLastCell) MsgBox myRng.Address<\/code><\/pre><p> Wanneer u deze code nu uitvoert, wordt er een berichtvenster weergegeven met het adres van de cel die het laatst is gebruikt (getypt). Dus hier in mijn geval gebruikte ik cel A10 en dezelfde ontving ik in het berichtenvenster.<\/p><h2 class=\"wp-block-heading\"> Selecteer cellen met notities<\/h2><p> In Excel zijn de oude \u201cOpmerkingen\u201d nu \u201cNotities\u201d. Wanneer u code schrijft om opmerkingen te selecteren, worden de opmerkingen binnen het opgegeven bereik geselecteerd. Beschouw de volgende code. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-54760\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/5-cellules-selectionnees-avec-des-notes.png\" sizes=\"auto, \" srcset=\"\" alt=\"selecteer cellen met notities\" width=\"590\" height=\"296\"><\/figure><\/div><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Dim myRng As Range Set myRng = _ Range(\"A1:A10\").SpecialCells(xlCellTypeComments) myRng.Select<\/code><\/pre><h2 class=\"wp-block-heading\"> Gebruik beide argumenten<\/h2><p> Zoals ik al eerder zei, heb je twee argumenten voor de SpecialCells-methode. Laten we nu eens kijken hoe u de twee argumenten kunt gebruiken om cellen te selecteren die een formule hebben, en dat de waarde die door de formule wordt geretourneerd een logische waarde is.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Dim myRng As Range Set myRng = Range(\"A1:A11\").SpecialCells(xlCellTypeFormulas, xlLogical) myRng.Select<\/code><\/pre><p> Wanneer ik nu de bovenstaande code uitvoer, worden cellen geselecteerd uit het bereik A1 tot A11, waar ik formules en logische waarden heb. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-54761\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/6-en-utilisant-les-deux-arguments.png\" sizes=\"auto, \" srcset=\"\" alt=\"gebruik van beide argumenten\" width=\"588\" height=\"243\"><\/figure><\/div><h2 class=\"wp-block-heading\"> Selecteer cellen met voorwaardelijke opmaak<\/h2><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Range(\"A1:A11\").SpecialCells(xlCellTypeSameFormatConditions) Range(\"A1:A11\").SpecialCells(xlCellTypeAllFormatConditions)<\/code><\/pre><h2 class=\"wp-block-heading\"> Selecteer zichtbare cellen<\/h2><p> En u kunt ook het zichtbare selecteren met behulp van de constante \u201cxlCellTypeVisible\u201d. Beschouw de volgende code. <\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Dim myRng As Range Set myRng = Range(\"A1:A11\").SpecialCells(xlCellTypeVisible) myRng.Select<\/code><\/pre><div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-54762\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/7-cellules-selectionnees-avec-mise-en-forme-conditionnelle.png\" sizes=\"auto, \" srcset=\"\" alt=\"selecteer cellen met voorwaardelijke opmaak\" width=\"607\" height=\"266\"><\/figure><\/div><p> Of u kunt ook &#8217;12&#8217; als argumentwaarde gebruiken.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Dim myRng As Range Set myRng = Range(\"A1:A11\").SpecialCells(12) myRng.Select<\/code><\/pre><h2 class=\"wp-block-heading\"> Cellen met gegevensvalidatie<\/h2><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Range(\"A1:A11\").SpecialCells(xlCellTypeAllValidation) Range(\"A1:A11\").SpecialCells(xlCellTypeSameValidation)<\/code><\/pre><div class=\"gb-container gb-container-4db6181c gb-container-mt\"> <a class=\"gb-button gb-button-c5897111 gb-button-text gb-button-mt-button\" href=\"https:\/\/exceladvisor.org\/nl\/vba\/\">Wat is VBA<\/a><h2 class=\"gb-headline gb-headline-665e5f6b gb-headline-text gb-headline-mt-heading\"> Gerelateerde tutorials <\/h2><div class=\"gb-grid-wrapper gb-grid-wrapper-a20baf7c gb-grid-wrapper-mt-grid\"><div class=\"gb-grid-column gb-grid-column-65ddf67e\"><div class=\"gb-container gb-container-65ddf67e\"><ul><li> <a href=\"https:\/\/exceladvisor.org\/nl\/aantal-lijnen\/\">Tel rijen met VBA in Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/karakter-lettertype\/\">Excel VBA-lettertype (kleur, grootte, type en vetgedrukt)<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/verberg-toon-kolomrij\/\">Excel VBA Verberg en toon een kolom of rij<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/bereik-1\/\">Excel VBA-bereik &#8211; Werken met bereik en cellen<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/grenzen\/\">Pas randen toe op een cel met VBA in Excel<\/a> <\/li><\/ul><\/div><\/div><div class=\"gb-grid-column gb-grid-column-50067b57\"><div class=\"gb-container gb-container-50067b57\"><\/div><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"ub_ctt_via":"","footnotes":""},"class_list":["post-2211","page","type-page","status-publish","hentry"],"featured_image_src":null,"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Hoe gebruik ik de speciale cellenmethode via VBA?<\/title>\n<meta name=\"description\" content=\"Deze zelfstudie laat zien hoe u de speciale cellenmethode in VBA gebruikt om toegang te krijgen tot cellen met een speciale eigenschap.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/\" \/>\n<meta property=\"og:locale\" content=\"nl_NL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hoe gebruik ik de speciale cellenmethode via VBA?\" \/>\n<meta property=\"og:description\" content=\"Deze zelfstudie laat zien hoe u de speciale cellenmethode in VBA gebruikt om toegang te krijgen tot cellen met een speciale eigenschap.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-06T05:48:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschatte leestijd\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/\",\"url\":\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/\",\"name\":\"Hoe gebruik ik de speciale cellenmethode via VBA?\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png\",\"datePublished\":\"2023-09-01T07:29:00+00:00\",\"dateModified\":\"2023-12-06T05:48:44+00:00\",\"description\":\"Deze zelfstudie laat zien hoe u de speciale cellenmethode in VBA gebruikt om toegang te krijgen tot cellen met een speciale eigenschap.\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#breadcrumb\"},\"inLanguage\":\"nl-NL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"nl-NL\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#primaryimage\",\"url\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Thuis\",\"item\":\"https:\/\/exceladvisor.org\/nl\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Hoe gebruik je de speciale celmethode?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#website\",\"url\":\"https:\/\/exceladvisor.org\/nl\/\",\"name\":\"Excel Advisor\",\"description\":\"Uw gids voor datadominantie\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/nl\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"nl-NL\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/nl\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"nl-NL\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hoe gebruik ik de speciale cellenmethode via VBA?","description":"Deze zelfstudie laat zien hoe u de speciale cellenmethode in VBA gebruikt om toegang te krijgen tot cellen met een speciale eigenschap.","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:\/\/exceladvisor.org\/nl\/speciale-cellen\/","og_locale":"nl_NL","og_type":"article","og_title":"Hoe gebruik ik de speciale cellenmethode via VBA?","og_description":"Deze zelfstudie laat zien hoe u de speciale cellenmethode in VBA gebruikt om toegang te krijgen tot cellen met een speciale eigenschap.","og_url":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/","og_site_name":"Excel Advisor","article_modified_time":"2023-12-06T05:48:44+00:00","og_image":[{"url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Geschatte leestijd":"3 minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/","url":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/","name":"Hoe gebruik ik de speciale cellenmethode via VBA?","isPartOf":{"@id":"https:\/\/exceladvisor.org\/nl\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#primaryimage"},"thumbnailUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png","datePublished":"2023-09-01T07:29:00+00:00","dateModified":"2023-12-06T05:48:44+00:00","description":"Deze zelfstudie laat zien hoe u de speciale cellenmethode in VBA gebruikt om toegang te krijgen tot cellen met een speciale eigenschap.","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#breadcrumb"},"inLanguage":"nl-NL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/nl\/speciale-cellen\/"]}]},{"@type":"ImageObject","inLanguage":"nl-NL","@id":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#primaryimage","url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png","contentUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-utiliser-la-methode-des-cellules-speciales-dans-vba.png"},{"@type":"BreadcrumbList","@id":"https:\/\/exceladvisor.org\/nl\/speciale-cellen\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Thuis","item":"https:\/\/exceladvisor.org\/nl\/"},{"@type":"ListItem","position":2,"name":"Hoe gebruik je de speciale celmethode?"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/nl\/#website","url":"https:\/\/exceladvisor.org\/nl\/","name":"Excel Advisor","description":"Uw gids voor datadominantie","publisher":{"@id":"https:\/\/exceladvisor.org\/nl\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/nl\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"nl-NL"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/nl\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/nl\/","logo":{"@type":"ImageObject","inLanguage":"nl-NL","@id":"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/2211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/comments?post=2211"}],"version-history":[{"count":1,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/2211\/revisions"}],"predecessor-version":[{"id":2557,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/2211\/revisions\/2557"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/media?parent=2211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}