CODEWITHSUNDEEP
vbaexcel
Jose Cortez
Jose Cortez

Reputation: 93

Exclude certain columns form range

Having trouble coding a macro in vba to exclude certain columns from being included in a chart. I want to exclude Column F from being included. Here is my code from a recoreded macro that I tweaked a bit: 

Sub Macro4()

Dim ws As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")






    ActiveSheet.Shapes.AddChart2(251, xlBarClustered).Select
    ActiveChart.SetSourceData Source:=ws.Range("E" & ActiveCell.Row & ":F" & ActiveCell.Row & ":G" & ActiveCell.Row & ":H" & ActiveCell.Row)
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$E$9:$H$9"
    ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
    ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesNone)
    ActiveChart.Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
    ActiveChart.FullSeriesCollection(1).DataLabels.ShowCategoryName = False
    With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Analysis for " & ws.Range("C" & ActiveCell.Row)
    ActiveChart.HasAxis(xlValue) = False
    ActiveChart.HasLegend = False
    End With



End Sub

Upvotes: 0

Views: 263

Answers (2)

urdearboy
urdearboy

Reputation: 14580

This should exclude F from your chart.

Edit:

To create your chart only using data from the active row, you can try: 

Sub Macro4()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
ws.Activate

    Dim MyRange1 As Range: Set MyRange1 = Range("E" & ActiveCell.Row)
    Dim MyRange2 As Range: Set MyRange2 = Range(Cells(ActiveCell.Row, "G"), Cells(ActiveCell.Row, "H"))
    Dim MyChartRange As Range: Set MyChartRange = Union(MyRange1, MyRange2)

    ActiveSheet.Shapes.AddChart2(251, xlBarClustered).Select
    ActiveChart.SetSourceData Source:=MyChartRange
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$E$9:$H$9"
    ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
    ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesNone)
    ActiveChart.Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
    ActiveChart.FullSeriesCollection(1).DataLabels.ShowCategoryName = False

With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Analysis for " & ws.Range("C" & ActiveCell.Row)
    ActiveChart.HasAxis(xlValue) = False
    ActiveChart.HasLegend = False
End With

End Sub

Upvotes: 1

QHarr
QHarr

Reputation: 84465

Try the following, you use "," to delimit non-consecutive ranges:

ActiveChart.SetSourceData Source:=ws.Range("Sheet1!$E$" & ActiveCell.Row & ",Sheet1!$G$" & ActiveCell.Row & ",Sheet1!$H$" & ActiveCell.Row)
ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$E$9,Sheet1!$G$9,Sheet1!$H$9"

Upvotes: 1

Related Questions