Trapping Dynamic Ranges in Excel VBA

Generating Dynamic Charts With VBA in Excel

Last Updated : 09 Jan, 2023

A powerful graph range is an information range that refreshes naturally when you change the information source. This unique reach is then utilized as the source information in an outline. As the information changes, the powerful reach refreshes right away which prompts an update in the outline.

The following is an illustration of a graph that utilizes a unique outline range.

Sample Data

Sample Data

Follow the further steps in Generating Dynamic Charts with VBA in Excel

Step 1: Opening the Visual Essential Window. Press ALT+F11 on your console to open the Visual Essential Window.

VBA code

Step 2: Embedding Another Module. Go to the Addition > Module choice in the toolbar. Click on Module. Another module called Module1 will be embedded.

VBA Module

Step 3: Putting the VBA Code

sub create _dynamic_ chart ()

Application . ScreenUpadting = False

with Activesheet. shapes ( Application .caller ) .Fill.Forecolor

If . Brightnesss = 0 Then

. Brightness = -0.150000006

Else

. Brightness = 0

End If

End With

Dim Sequence ( ) As String

Desired__shapes = Array ( " Rounded Rectangle 1" , " Rounded Rectangle 2 " , " Rounded Rectangle

For i = LBound ( Desired _Shapes ) To UBound ( Desired_Shapes)

With ActiveSheet.Shapes(Desired _Shapes (i) )

If .Fill . Forecolor.Brightness = -0.150000006 Then

Sequence ( UBound ( Sequence ) ) = . Text Frame2.TextRange . Characters . Text

ReDim Preseve Sequence ( UBound ( Series ) + 1 )

End If

End With

Next i

If UBound ( Sequence ) >0 Then ReDim Preserve Sequence ( UBound ( Sequence ) - 1 )

Worksheets ("sheet 1" ) . ListObjects ( " Table 1 " ). Range . AutoFilter Field :=1

Worksheets ( " sheet 1"). ListObjects ( "Table 1 " ) . Range .AutoFilter _

Field : =1 , Criterial : = Sequence , Operator : =xl FilterValues

Application.ScreenUpadting = True

End Sub

Step 4: Saving the Workbook in XLSM Format, then return to the workbook and save it as an Excel Macro-Enabled Workbook.

Excel Macro-Enabled Workbook

Step 5: Run the code from the Run Sub/UserForm choice in the toolbar.

Run VBA

You'll find a powerful graph made in view of the table in Sheet2 of the worksheet.

Dynamic Chart

Trapping Dynamic Ranges in Excel VBA

S

sruthiprikhed

Improve

Article Tags :

Similar Reads

How to Make a Dynamic Gantt Chart in Excel?

The Gantt chart is named after Henry Gantt, an American mechanical engineer and management consultant who devised it in the 1910s. In Excel, a Gantt diagram displays projects or tasks as cascading horizontal bar charts. A Gantt chart depicts the project's breakdown structure by displaying start and

Dynamic Organizational Chart in Excel

An Organization Chart is also referred to as An Org Chart. This chart is used to show the internal structure and hierarchy of different roles in an organization, a company, or a college. An Organization Chart can also be beneficial to illustrate reporting system in an organization, which means it wi

Trapping Dynamic Ranges in Excel VBA

Dynamic Ranges give the flexibility to work with more range of cells as compared to a specific range of cells which usually limits us to work with a particular range of cells. As the size of the data may change in an excel sheet so it is also necessary to change the range in the code dynamically for

Formatting Charts in Excel

One of the major uses of Excel is to create different types of charts for a given data set. Excel provides us with a lot of modification options to perform on these charts to make them more insightful. In this article, we are going to see the most common "Formatting" performed on charts using a suit

Excel Dynamic Chart Linked with a Drop-down List

Dynamic Chart using drop-down list is very helpful when we deal with tons of grouped data and perform comparative analysis. For example, an E-commerce site sells different types of products. They can create a drop-down list for every product and in the chart, they can see the sales details in the la

Dynamic Map with Drop-Down in Excel

The map charts are generally used to compare the data values and show different categories across the geographical region. In excel we use map char to visualize the KPI (Key Performance Indicator) and represent the distribution of KPI across multiple geographical regions for any specific category. I

Programming Charts in Excel VBA

VBA stands for Visual Basic for Applications and it is developed by Microsoft. MS Excel and many other Microsoft applications like word, access, etc have this language integrated into them using which one can do various things. VBA can be used in MS Excel through the code editor in its developer tab

How to Create a Dynamic Chart Range in Excel?

A Dynamic chart range is the range of a data set which automatically updates on any modifications in the original data set. It is beneficial because at some point in time we need to add or delete data from the original data set. So, we want a method to automatically update the chart on performing an

How to Create a Dynamic Pie Chart in Excel?

In Excel, Pie-chart is a graphical representation of different sections or sectors of a circle based on the proportion, it holds from the complete quantity. Pie-charts are generally categorized into two types: Static Pie-chart: A pie-chart created with static or fixed input values is known to be a s

Exploring Data with Power View in Excel

Microsoft Excel's Power View is a complex visualization function that allows users to create graphs, charts, and reports. It makes it simple for organizations to create reports and dashboards that can be shared with stakeholders, managers, and other team members on a daily, weekly, and monthly basis