VBA Ranges using Cells

[nzepeda]

In the macro I am using I am defining my Range like this:

    Set LosBcolran = Numb.Range("B6:B21,B23")
    Set LosCcolran = Numb.Range("C6:C21,C23")
    Set LosDcolran = Numb.Range("D6:D21,D23")
    Set LosEcolran = Numb.Range("E6:E21,E23")
    Set LosFcolran = Numb.Range("F6:F21,F23")
    Set LosGcolran = Numb.Range("G6:G21,G23")
    Set LosHcolran = Numb.Range("H6:H21,H23")

I was curious as to whether or not I could use the Cells Property, this would help in allowing me to shorten up my macro.

I was trying to make it into something like this:

Range(Cells(6,2):Cells(21,2), Cells(23,2))
etc.

I know that I can write this in vba without any issues:

Range(Cells(6,2), Cells(23,2))

Thing I can not get to work is when I do not use 1 of the cells that is between that range.

I know that the 2nd example of code I have would not work with the ":", I would have to use a "," like I did for the 3rd example.

In summary what I need to find out is how to write a Range that uses the Cell property that goes from Cell(6,2) to Cell(21,2) that will also include Cell(23,2).

My first example does this, but I do not want to use "B6", it must be in Cell format.

Any help would be appreciated.

 

[dave3009]

You probably want to look at Resize

Cells(6,2).Resize(0, 15)

 

[nzepeda]

Resize seems to either increase or decrease a Range by a Row or Column. I do not see how I could get this to work for trying to create a Range that uses Cells and is not continuous.

Any examples you could provide would be helpful.

 

[jim may]

I put the below in the immedaite wndow as follows:
Enter Line 1 and press Enter;
Enter Line 2 and you should get line 3, proving that line 1 is correct....


Union(Range(Cells(6, 2),Cells(21,2)),Range(Cells(23,2),Cells(23,2))).Select
? selection.address
$B$6:$B$21,$B$23

 

[Greg Truby]

Why all of the use of CELLS? Just to tighten up code? If so, I'd probably use an array and loop. It's also much more scalable that way.

Sub Ejemplo()
    Const c_strStartingAddress As String = "B6:B21,B23"
    Dim rngColumnsRun(0 To 6) As Excel.Range
    
    Set rngColumnsRun(0) = Sheet1.Range(c_strStartingAddress)
    
    For i = 1 To UBound(rngColumnsRun)
        Set rngColumnsRun(i) = rngColumnsRun(0).Offset(, i)
        Debug.Print rngColumnsRun(i).Address(0, 0)
    Next i
End Sub

Also, just tossing in 2 pesos worth... avoid Spanglish or Spanish variables names unless your code is going to be read by Spanish speakers.

 

[tusharm]

Why create variables (or array elements) when they will be used one at a time -- unless, of course, that's an incorrect conclusion. Just use a loop with a single range.

    Dim I As Integer
    For I = Columns("B").Column To Columns("h").Column
        Dim CurrRng As Range
        Set CurrRng = Application.Union(Cells(6, I).Resize(16), Cells(23, I))
        Debug.Print CurrRng.Address 'do the work for this column here
        Next I

Why all of the use of CELLS? Just to tighten up code? If so, I'd probably use an array and loop. It's also much more scalable that way.

Sub Ejemplo()
    Const c_strStartingAddress As String = "B6:B21,B23"
    Dim rngColumnsRun(0 To 6) As Excel.Range
    
    Set rngColumnsRun(0) = Sheet1.Range(c_strStartingAddress)
    
    For i = 1 To UBound(rngColumnsRun)
        Set rngColumnsRun(i) = rngColumnsRun(0).Offset(, i)
        Debug.Print rngColumnsRun(i).Address(0, 0)
    Next i
End Sub

Also, just tossing in 2 pesos worth... avoid Spanglish or Spanish variables names unless your code is going to be read by Spanish speakers.

 

[Greg Truby]

Hiya, Tushar. Trust you've been well. (Are you stalking me? )

Why create variables (or array elements) when they will be used one at a time -- unless, of course, that's an incorrect conclusion. Just use a loop with a single range.

There's a decent probability that you are correct and that only one base variable is even needed at all. Without the context, it's tough to say. My main objective was to broaden zepeda's perspective. It looked to me like he had developed a bit of tunnel vision, focusing on forcing the CELLS() referencing to work instead of looking at other structures or architectures that would be equally functional but more scalable.