Select multiple columns from CurrentRegion property to create a bar chart

[taykaisheng]

Hi, Could anyone teach me how to select multiple columns in CurrentRegion property so that I can create a Bar chart based on it


Below are my progress so far.. however it doesn't work

 Public Function FourthTable()


    Dim Source As Workbook
    Dim Search As Range
    Dim CR As Range


    SRT = "Service Request Tickets (IIT)"
    
    Set Source = Workbooks.Open("C:\Users\HP\Desktop\test.xlsx")
    
    Source.Worksheets("Sheet1").Activate
    
    Set Search = ActiveSheet.Cells.Find(SRT)
    
    Source.Worksheets("Sheet1").Range(Search.Address).Offset(2, 0).CurrentRegion.Activate
   
    ActiveCell.CurrentRegion.Columns.Range("A:A, 
    C:C").Resize(ActiveCell.CurrentRegion.Rows.Count - 1).Select
    
    ActiveSheet.Shapes.AddChart.Select
    'ActiveChart.SetSourceData  
    Source:=ActiveWorkbook.Sheets(1).Range(Search.Address).Offset(2, 
    0).CurrentRegion.Resize(CR.Rows.Count - 1, CR.Columns.Count)
    ActiveChart.ChartType = xl3DBarClustered
    With ActiveChart
       .Perspective = 0
       .Elevation = 15
       .Rotation = 20
       .RightAngleAxes = True
        
    End With
    
    ActiveSheet.ChartObjects(1).Activate
    ActiveSheet.ChartObjects(1).Cut
    With Sheets("Sheet2")
    .Select
    .Range("A34").Select
    End With
   
    ActiveSheet.Paste
    Source.Close SaveChanges:=True
    End Function

The error message i got was Application-defined or Object-defined error


The columns I wanted to select was Column A and Column C. I am unable to select the column by indicating the address of the cells such as "A34" or "C34" because the table data was generated using a program tools and thus the location of the table data is vary every time


The image below is the table data that I refer to


View image: table 1

 

[JLeno]

To select the columns A and C of the active cell's currentregion, you can use the following:

Application.Intersect(ActiveCell.CurrentRegion, Union(Range("A:A"), Range("C:C"))).Select

Please note that you don't have to Select and Activate cells, you can also put the range you need immediately in the relevant place of the macro without it being selected on the sheet.

 

[taykaisheng]

Hi thanks for your answer, but it still don't work with same error message as previous

Public Function FourthTable()

Dim Source As Workbook
Dim Search As Range
Dim CR As Range


    SRT = "Service Request Tickets (IIT)"
    
    Set Source = Workbooks.Open("C:\Users\HP\Desktop\test.xlsx")
    
    Source.Worksheets("Sheet1").Activate
    
    Set Search = ActiveSheet.Cells.Find(SRT)
    
    With Source.Worksheets("Sheet1").Range(Search.Address).Offset(2, 0).Activate
    Application.Intersect(ActiveCell.CurrentRegion, Union(Range("A:A"), Range("C:C"))).Select
    'ActiveCell.CurrentRegion.Columns.Range("A:A, C:C").Resize(ActiveCell.CurrentRegion.Rows.Count - 1).Select
    End With
    
    ActiveSheet.Shapes.AddChart.Select
    'ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets(1).Range(Search.Address).Offset(2, 0).CurrentRegion.Resize(CR.Rows.Count - 1, CR.Columns.Count)
    ActiveChart.ChartType = xl3DBarClustered
    With ActiveChart
       .Perspective = 0
       .Elevation = 15
       .Rotation = 20
       .RightAngleAxes = True
        
   End With
    
  ActiveSheet.ChartObjects(1).Activate
  ActiveSheet.ChartObjects(1).Cut
  With Sheets("Sheet2")
  .Select
  .Range("A34").Select
  End With
   
  ActiveSheet.Paste
  Source.Close SaveChanges:=True
End Function

Please advice.. thanks so much