TheSmallman.com

an XL ideas Lab

  • Home
    • Excel Dashboard Course
    • Financial Modelling Course
    • Excel Infographics Course
    • Excel VBA Course
  • Models
  • Native Excel
  • Charts
  • Dashboards
  • Infographics
  • Modelling
  • VBA
  • Blog
  • About
Menu

Excel Dashboards VBA and more

Street Address
City, State, Zip
Phone Number
an XL ideas Lab

Your Custom Text Here

Excel Dashboards VBA and more

  • Home
  • Courses
    • Excel Dashboard Course
    • Financial Modelling Course
    • Excel Infographics Course
    • Excel VBA Course
  • Models
  • Native Excel
  • Charts
  • Dashboards
  • Infographics
  • Modelling
  • VBA
  • Blog
  • About

Range Areas in Excel with VBA

March 15, 2018 Marcus Small
VBA Range Area

Have you ever had to deal in someone else’s data and it is full of spare lines and dodgy formatting.  It is frustrating from a programming perspective as it is often difficult to coral data when it is dispersed.  Enter the power of the area within a range.  You can trap data in an area and manipulate it then move onto the next area in a given range.  It is a wonderful part of the VBA language and like anything having it in your tool kit might be just the ticket one day – you never know when you will have to draw on this Excel knowledge.  My time came this week when a client wanted me to build an individual invoice from the ground up like 140 times.  The output needed to be in a weird shape with lots of lines between each dataset.  I knew about areas but had not used them in a practical way.  So I drew on my knowledge to kick the ball into the back of the net. 

The above shows how the data is set out with gaps between each dataset.  It can be very frustrating to work with when data is not tabular.

The above shows how the data is set out with gaps between each dataset.  It can be very frustrating to work with when data is not tabular.

 

This is how you can trap an area in column A.  In my example the data is split into data which is separated into lines. I want to manipulate each dataset in turn.  Here is how you do it with an area.  We will create a simple loop then trap each area in turn.

Option Explicit

Sub LoopArea()
Dim lr As Long
Dim area As Range
Dim sh As Worksheet

Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    'Constant based Area in Col A
    For Each area In sh.Range("A13:A" & lr).SpecialCells(xlCellTypeConstants, 23).Areas
       area.Interior.Color = vbGreen
    Next area
    'Formula based Area in Col H
    For Each area In sh.Range("H13:H" & lr).SpecialCells(xlCellTypeFormulas).Areas
       area.Interior.Color = vbBlue
    Next area
End Sub

 For Each area In sh.Range("A13:A" & lr).SpecialCells(xlCellTypeConstants, 23).Areas

        area.Interior.Color = vbGreen

Next area

The second loop iterates through formulas in column H.  This allows you a different way to iterate through an area.

For Each area In sh.Range("H13:H" & lr).SpecialCells(xlCellTypeFormulas).Areas

        area.Interior.Color = vbBlue

Next area

It will make the relevant areas blue.  I have included an Excel file as a sample to show how the technique might work.  You could take the whole process a step further and add things like formula on the fly.  Here is how you might do that to Column i.

Option Explicit

Sub LoopAreaFormula()
Dim lr As Long
Dim area As Range
Dim shAs Worksheet

Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    'Formula based Area in Col i
    For Each area In sh.Range("H13:H" & lr).SpecialCells(xlCellTypeFormulas).Areas
       area.Offset(, 1).FormulaR1C1 = "=RC[-2]*R9C9"
    Next area
End Sub

You can see after you run the procedure that a new column of formula is added for all the areas in a given range. It skips all of the blanks and works beautifully.

Here is the Excel file to go with the post.

 

Areas.xlsm

 

 

 

 

Tags Excel, Range, Area, Loop
← Social Media Dashboard in ExcelRevenue and Expense Infographic Construction Industry →

Featured Posts

Evaluate and Filter Part 2

Evaluate and Filter Part 2
April 26, 2015

 Showing Trends with a Chart

Showing Trends with a Chart
March 5, 2015

Excel VBA to Sort an Array

Excel VBA to Sort an Array
February 23, 2015

 Excel VBA Slicer Selection

Excel VBA Slicer Selection
January 15, 2015

Recent Posts

Protect a Worksheet, Use the Autofilter

Protect a Worksheet, Use the Autofilter October 18, 2018

Countifs Across Sheets

Countifs Across Sheets October 10, 2018

Excel VBA to Select Sheets

Excel VBA to Select Sheets September 22, 2018

Power BI Desktop Dashboard

Power BI Desktop Dashboard August 20, 2018

New Excel Dashboard Tools

New Excel Dashboard Tools August 1, 2018

Watch This Space

Watch This Space July 11, 2018

The Fuzzy World of Financial Modelling

The Fuzzy World of Financial Modelling May 31, 2018

Excel Autofill - Fill Down to Bottom of Range

Excel Autofill - Fill Down to Bottom of Range April 8, 2018

Social Media Dashboard in Excel

Social Media Dashboard in Excel April 5, 2018

Range Areas in Excel with VBA

Range Areas in Excel with VBA March 15, 2018

 

TrainiNg

  • Dashboarding with Excel
  • Visual Analytics
  • Advanced Dashboard Design in Excel
  • Financial Modelling in Excel
  • Advanced Financial Modelling in Excel

 

  • 3 Statement Modelling in Excel
  • Project Financial Modelling
  • PowerPivot
  • Excel Automation with VBA
  • Financial Modelling with VBA

Follow US:

 

marcussmall@thesmallman.com
 

TheSmallman.com - Making your small systems hum...
© Copyright theSmallman.com All Rights Reserved.