Dynamic Ranges

[NatalieEC]

Hello,

I know that I can use this line of code to find a range and the last value of the column.

Set Range = Range("B11", Range("B" & Rows.count).End(xlUp))

Is there a way to do this with out using "B"? For example, I would like "B" to be a variable that goes through a loop and is updated.

Thank you, Natalie

[Norie]

What are you looping through?

[NatalieEC]

What I would like to do (I think) is use a while loop. If a particular condition passes, I would like to update the column number and check a different column for a maximum value. The columns I need to check are eight columns apart, so I would update a variable.

[Arkadi]

You can simply make "B" a variable?

Dim mycol As String
Dim myrange As Range
mycol = "B" 'or however you want to assign the variable's value
Set myrange = Worksheets("Sheet1").Range(mycol & "11", Range(mycol & Rows.Count).End(xlUp))

[NatalieEC]

Thank you for your response.

I am trying that. I have many columns of data. Each are 8 columns apart. In a summary file, I'd like to to put the maximum value of each column in a new column. So far it isn't work. Here is my code if you can take a look at it.

Dim n As Integer
Dim m As Integer
Dim i As Integer
Dim Cell As String
Dim idss As Double
Dim col As Integer
Dim vbrRange As Range



n = 2                   'start with column B
i = 11                  'first row in column reticle
m = 3
col = 1

Cell = ActiveWorkbook.Sheets("Idss").Cells(1, n).Value
idss = ActiveWorkbook.Sheets("Idss").Cells(126, 3).Value

Sheets("Idss").Activate
Set vbrRange = Sheets("Idss").Range(col & "11", Range(col & Rows.count).End(xlUp))

While Cell <> ""
    
    Cells(i, 1) = Cell
    Cells(i, 2) = Application.WorksheetFunction.Max(vbrRange)
    n = n + 8
    col = col + 8
   Set vbrRange = Sheets("Idss").Range(col & "11", Range(col & Rows.count).End(xlUp))
    Cell = ActiveWorkbook.Sheets("Idss").Cells(1, n).Value
    
    i = i + 1

    
Wend