{"id":2364,"date":"2015-03-31T11:33:34","date_gmt":"2015-03-31T11:33:34","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2364"},"modified":"2023-12-31T00:05:52","modified_gmt":"2023-12-31T00:05:52","slug":"specialcells-in-vba","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/specialcells-in-vba\/","title":{"rendered":"SpecialCells in VBA"},"content":{"rendered":"

How to use SpecialCells method in Excel VBA<\/h2>\n

SpecialCells\u00a0in VBA is a really useful method to deploy in Excel. It returns a Range Object<\/b> that only covers the type of cells\u00a0you specify. You can use the SpecialCells in VBA Method to return a Range Object<\/b> that only holds numbers, text, blank cells, formulae, cells with datavalidation, cells with conditional formatting, the last cell in the worksheet, cells with comments and all visible cells.<\/p>\n

If you for example want to change formatting for all numbers in a worksheet you do not need more than one line in the Visual Basic Editor <\/strong>to do it.<\/p>\n

Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Style = “currency”<\/strong><\/p>\n

This line will change all numbers in the active worksheet to currency format.\u00a0 The Range object Cells<\/strong> is used to tell Excel that you want to look at all the cells and the special cells method to decrease it to in this example only constants (xlCellTypeConstants<\/strong>) and again to decrease it to only numbers the criteria \u00a0xlNumbers<\/strong> is added to the SpecialCells<\/strong> Method.<\/p>\n

Similar we can use the SpecialCells<\/strong> Method to\u00a0return a Range Object<\/b> that only holds text.<\/p>\n

Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Font.ColorIndex=3<\/strong><\/p>\n

This VBA line will change the font colour to red for all text in the active worksheet.<\/p>\n

The SpecialCells<\/strong> Method<\/strong> syntax is<\/strong>;
\nexpression<\/i>.SpecialCells(Type, Value)<\/p>\n

The Expression have to be a Range object<\/strong> such as Cells, Range(“A1:B200”), ActiveSheet.UsedRange <\/strong>etc.<\/p>\n

The different types of special cells are:<\/p>\n

    \n
  1. xlCellTypeAllFormatConditions\u00a0(all formatted cells)<\/li>\n
  2. xlCellTypeAllValidation (all cells with datavalidation)<\/li>\n
  3. xlCellTypeBlanks (all blank cells)<\/li>\n
  4. xlCellTypeComments (all cells\u00a0with notes)<\/li>\n
  5. xlCellTypeConstants (all cells containing\u00a0constants (numbers or text))<\/li>\n
  6. xlCellTypeFormulas (all cells\u00a0with formulas)<\/li>\n
  7. xlCellTypeLastCell<\/span> (<\/span>The last cell in\u00a0all used ranges)<\/li>\n
  8. xlCellTypeSameFormatConditions (all cells\u00a0with the same formatting also conditional formatting)<\/li>\n
  9. xlCellTypeSameValidation (all\u00a0 cells\u00a0with the same datavalidation)<\/li>\n
  10. xlCellTypeVisible (alll visible cells)<\/li>\n<\/ol>\n

    You can also use a combination of the above options.<\/p>\n

    Cells.SpecialCells(xlCellTypeConstants, xlNumbers).SpecialCells(xlCellTypeAllValidation).Font.Color = vbRed<\/strong><\/p>\n

    This line of VBA code will add red font colour to all cells with numbers & Datavalidation.<\/p>\n

    The SpecialCells <\/strong>in VBA Method is also very powerful if you want to test your data in an If Then Else <\/strong>decision code.<\/p>\n

    \"Capture\"<\/a>
    SpecialCells in VBA<\/figcaption><\/figure>\n

     <\/p>\n

    In the example above all\u00a0numbers are tested in the active worksheet if the value is greater than 7500. If the test is true 10% is added. The For Each <\/strong>loop is only running through cells with numbers.<\/p>\n

    The SpecialCells <\/strong>in VBA Method\u00a0can be very handy if you need to remove blank rows from you Excel lists or Excel databases.<\/p>\n

    \"Capture\"<\/a><\/p>\n

    and after running the macro<\/p>\n

    \"Capture2\"<\/a><\/p>\n

    In the above example The SpecialCells<\/strong> Method\u00a0finds all blank cells in the range from A3 to A27 and deletes the entire row.<\/p>\n

    You have a lot of variations you can use and you will find out that when you start using The SpecialCells<\/strong> method\u00a0\u00a0you will save a lot of lines in your macros!<\/p>\n","protected":false},"excerpt":{"rendered":"

    How to use SpecialCells method in Excel VBA SpecialCells\u00a0in VBA is a really useful method to deploy in Excel. It returns a Range Object that only covers the type of cells\u00a0you specify. You can use the SpecialCells in VBA Method to return a Range Object that only holds numbers, text, blank cells, formulae, cells with […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,6,18,19],"tags":[505,506,507],"class_list":["post-2364","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-hints-tips","category-technology","category-vba-training","tag-special-cells-method-excel-2003-vba","tag-specialcells-in-vba","tag-specialcells-vba-excel-2013"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2364"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2364\/revisions"}],"predecessor-version":[{"id":6683,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2364\/revisions\/6683"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2364"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}