Select area escpecify in excel VBA spreadsheet

Question

I have the table below in the photo, and would like to create a stacked area chart based on benchmarks (column values). I’m using the following code

Private Sub CommandButton1_Click()
Dim planilhaB As Worksheet
Dim rng   As range

Set planilhaB = ThisWorkbook.Worksheets("Planilha2")

planilhaB.range("A1").Select
planilhaB.range(Selection, Selection.End(xlToRight)).Select
planilhaB.range(Selection, Selection.End(xlDown)).Select
Set rng = Selection

End Sub

But she also selects the column Total of the Day. I don’t want to reference cells pq may be that new columns are added in the future, before the Total of the Day column.

Answer 1

If your spreadsheet does not have other discontinuous populated areas, it would be reasonably simple to use UsedRange. For example:

Private Sub CommandButton1_Click()
    Dim planilhaB As Worksheet
    Dim rng As Range, rngCelInicio As Range, rngCelFim As Range

    Set planilhaB = ThisWorkbook.Worksheets("Planilha2")

    With planilhaB
        Set rngCelInicio = .UsedRange.Cells(1, 1)
        Set rngCelFim = .UsedRange.Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)
        Set rng = Range(rngCelInicio, rngCelFim.Offset(0, -1))
    End With
End Sub

Answer 2

To not use fixed references and search for the title, you can use the Excel function Match() and get the column number of a starting point and an ending.

With this, you can Select table data and you can add more columns between the initial column and the final column.

Code

Dim planilhaB As Worksheet
Dim rng   As Range
Dim colunaData As Long, colunaTotalDoDia As Long

Set planilhaB = ThisWorkbook.Worksheets("Planilha2")

With planilhaB
    'Rows(1): para procurar o título na linha 1
    colunaData = Application.WorksheetFunction.Match("Data", .Rows(1), 0)
    colunaTotalDoDia = Application.WorksheetFunction.Match("Total do Dia", .Rows(1), 0)
    
    '.Cells(2: os dados iniciam na linha 2
    .Range(.Cells(2, colunaData), .Cells(2, colunaTotalDoDia - 1)).Select
    .Range(Selection, Selection.End(xlDown)).Select

End With
Set rng = Selection

Obs.: .Select/. Activate/. Activecell should be avoided almost always (except to activate events or other occasions that is possible only with Select).

Answer 3

I used the following code

planilhaB.Range("A1").Select
planilhaB.Range(Selection, Selection.End(xlToRight)).Select 
Dim rng as range
Dim rntLastColumn as range
Set Rng = planilhaBenchmark.Range(Selection, Selection.End(xlDown))
Set rngLastColumn = Rng.Resize(Rng.Rows.Count, Rng.Columns.Count - 1)