How to use column/row index as range in VBA

Question

Like using Cells(1, 1) instead of Range("A1"), what's the best way to use column/Row index as range in VBA?

I came up with 2 solutions to denote Range("A:A"):

Range(Cells(1, 1), Cells(Columns(1).Rows.count, 1))
Union(Columns(1), Columns(1))

Is there a better and more concise solution?

Edit: noted response from Tehscript and thanks for the same. I already tried that but it's giving below error:

Run-time error '13': Type mismatch.

Here's the code:

Sub tfind()
    Dim r1 As Range
    Set r1 = Columns(1)
    MsgBox mCount("Test:", r1)
End Sub
Function mCount(find As String, lookin As Range) As Long
   Dim cell As Range
   For Each cell In lookin
       If (Left(cell.Value, Len(find)) = find) Then mCount = mCount + 1
   Next
End Function

Although it works fine if the 3rd line:

Set r1 = Columns(1)

is changed to:

Set r1 = Union(Columns(1), Columns(1))

If you had posted your code earlier, it would be easier to get a proper answer. What you are looking for is .Cells — Tehscript
– Tehscript, Commented Jun 4, 2017 at 20:41

Tehscript · Accepted Answer · 2017-06-04 20:38:55Z

6

There is no best way to do this, but there are ways that you can use according to your needs. For example if you want to loop through both rows and columns you should better use Cells():

Sub RowTimesColumn()
Dim i As Long, j As Long
For i = 1 To 10
    For j = 1 To 5
        Cells(i, j) = i * j
    Next j
Next i
End Sub

On the other hand you can reference a range like Range("A1:B3") in either way depending on your needs. If you simply need the reference, you should use Range("A1:B3"). If you need to play with the rows and columns, you should better use Range(Cells(1, 1), Cells(3, 2)).

It is all about readability and functionality.

For your question, you might want to use the following:

Range("A:A") --> Columns(1)
Range("A:C") --> Range(Columns(1), Columns(3))

Edit: You are looping through the cells within Column A, in that case you need to use:

Columns("A").Cells or Columns(1).Cells

edited Jun 4, 2017 at 20:38

answered Jun 4, 2017 at 0:40

Tehscript

2,5562 gold badges15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NotTheDr01ds · Accepted Answer · 2024-08-13 18:21:03Z

this is my first post I tried to find a solution to a problem and could not find the exact topic, this is close.

basic problem I need my row and column dynamically find my test data to copy to a different sheet

Sheets(1).Range("B" & firstrow, "L18").Copy

this worked until the header went missing .

Sheets(1).Range("B" & firstrow, "L" & lastrow).Copy

this worked until the size of the matrix changed in width.

so I needed dynamic row start, row end, column start and column end this was not working until I added "sheets(1)" to the cell reference, which was the original problem I created this account for

Sheets(1).Range(Sheets(1).Cells(firstrow, firstcol + 1), Sheets(1).Cells(lastrow, lastcol)).Copy

" + 1" since there is a date stamp I could not avoid in my CSV export .

I much prefer dynamic solutions over fixed cell references As to how to get first and last row/column I have a standard I reuse and adjust : in this case A1:D7 has header information on the system, the data starts at B8 , with time stamps in A8:

firstrow = Sheets(1).Range("E100", Range("E1").End(xlDown)).Row

lastrow = Sheets(1).Range("E100", Range("E8").End(xlDown)).Row

nrow = lastrow - firstrow - 1

helps me ignore the header and locate the first and last row , the counts number of rows for reference

firstcol = Sheets(1).Range("J1").End(xlToLeft).Column

lastcol = Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Column

ncol = lastcol - firstcol

First column ensures I can check for data existing, in my case the header would post but no data coming out would keep it empty , firstcol = lastcol I use as a break point to exit if true

Collectives™ on Stack Overflow

How to use column/row index as range in VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related