excel - Using a variable for row in (row,column) for a range in VBA

Question

I have a global variable called processRowBegin that contains the row number for the beginning of a process. I also have a global variable called processRowEnd that contains the row number for the end of a process.

What I am trying to do is create a chart for two columns, Columns C and E on the active worksheet, where the range for the selected rows to be plotted is from processRowBegin to processRowEnd. The problem that I am coming across is that I do not know how to use a range with a variable for the row in (row, column) syntax. I have researched this question and so far have not found a solution that works. I tried using the (row,column) syntax at first with the variable names for the row in (row, column). This did not work and resulted in the error in the picture [Using variable for row in range error][1}.

I then tried to implement a solution (code below) that I found via another user's question but still receive the same error. How does one go about doing this in VBA?

Range(("C" & processRowBegin):("C" & processRowEnd)).Select
Range(("E" & processRowBegin):("E" & processRowEnd)).Select

I am a beginner in Excel VBA as well as programming and would appreciate help. This is my last step to complete an important work project. If anyone would like to view my entire code, please let me know.

Answer 1

You would do it this way:

Range("C" & processRowBegin & ":C" & processRowEnd).Select

Range takes either two cells or a string to denote the extents.

To do it by the two cells:

Range(Cells(processRowBegin,3),Cells(processRowEnd,3)).Select

A couple notes:

1. One should avoid using select and simply do what is desired with the cells. For more information on how to avoid it see this POST

2. One should always qualify all all range objects to their parent sheets. If using the Cells() they too need to be qualified

Example

With WorkSheets("Sheet1")
    .Range(.Cells(processRowBegin,3),.Cells(processRowEnd,3)).Value = .Range(.Cells(processRowBegin,5),.Cells(processRowEnd,5)).Value
End With