How to Create an Excel Chart from a Selected Range Using VBA
Question details
The user wants to modify an existing VBA macro to generate a chart based exclusively on the currently highlighted cells instead of charting a fixed range or an entire region.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating dynamic chart creation using VBA where the data scope depends on the user's real-time worksheet selection.
- Observed behavior
- Existing macros use properties like EntireRow or CurrentRegion, which plot all contiguous data and include unwanted cells rather than isolating the specific user-selected range.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled in your ribbon to access the VBA Editor.
Use the Selection Object as the Chart Source
Change the data source reference in your VBA code from CurrentRegion or EntireRow to Selection to plot exactly what the user highlights.
When VBA macros use CurrentRegion or UsedRange, they automatically grab all contiguous data around the active cell. Using the Selection property guarantees the chart relies solely on the exact cells you highlighted before running the macro.
Press Alt + F11 to launch the VBA Editor, then click Insert > Module to create a new module.
Create a new Sub procedure (e.g., Sub CreateChartFromSelection()) and declare a Chart variable using 'Dim C As Chart'.
Set the chart variable to a new chart shape, then assign the source data using the code: C.SetSourceData Source:=Selection.
Return to your worksheet, highlight the specific row or cells you want to chart, and run the macro to generate the targeted visualization.
Prompt for a Data Range using an InputBox
If you prefer to manually confirm or adjust the range at runtime before the chart is created, use an InputBox to capture the target range.
Create Dynamic Charts Instantly in WPS Spreadsheet
WPS Spreadsheet offers robust VBA and Macro support, allowing you to run your existing Excel macros seamlessly. You can easily automate chart generation from selected ranges without changing your workflow.
- 1. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing your chart data.
- 2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to manage your macros.
- 3. Insert the Selection Macro: Paste your customized VBA code utilizing the 'Selection' object to ensure only highlighted cells are charted.
- 4. Highlight and Run: Select the exact data range in your spreadsheet, then run the macro to instantly generate your tailored chart.
Frequently Asked Questions
Why does my VBA chart include blank rows or extra data?
If your macro uses properties like 'CurrentRegion' or 'EntireRow', it selects all contiguous cells or the whole row respectively. Changing the source to 'Selection' restricts the chart data to just the cells you have manually highlighted.
Can I use this macro on non-contiguous selected ranges?
Yes, VBA's 'Selection' object can handle multiple non-adjacent ranges (e.g., holding the Ctrl key while selecting cells), but ensure that your chosen chart type supports plotting non-contiguous data properly.
How do I assign my chart creation macro to a custom button?
Go to the Developer tab, insert a Form Control Button onto your worksheet, right-click the new button, select 'Assign Macro', and choose your dynamic chart creation macro from the list.
