How to express my cell range dynamically (using numbers) in VBA?

[Ognox]

I'm trying to fill down a column but it's a looped operation and has to be dynamic.

Works:
Cells(3, 8).AutoFill Destination:=Range("H3:H" & 30)

How do I change "H3:H" so that it would be expressed in numbers dynamically, i.e. I could later replace those numbers to variable definitions. In other words, I cannot deal with letter ranges in my code as another loop may be a different range, e.g. "G3:G" etc.

[JKwan]

give this a go

Sub FillToLastRow()
Dim LastRow As Long
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
LastRow = FindLastRow(WS, "H")
Cells(3, 8).AutoFill Destination:=Range("H3:H" & LastRow)
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[p45cal]

H3:J13 is range(cells(3,8),cells(13,10))

cells(row no.,column no.)

[Ognox]

H3:J13 is range(cells(3,8),cells(13,10))

cells(row no.,column no.)

I am aware of this but please show me then how you can write "H3:H" using Range(Cells... and then add "& 30" in the same range as I am required by definition.

[Ognox]

give this a go

Sub FillToLastRow()
Dim LastRow As Long
Dim WS As Worksheet

Set WS = Worksheets("Sheet1")
LastRow = FindLastRow(WS, "H")
Cells(3, 8).AutoFill Destination:=Range("H3:H" & LastRow)
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function



I don't see how this is helpful as you are still showing me the same line that does not fit my intention. I know how to find my number of rows (Lastrow is not the issue here), the problem is in the range definition:

Range("H3:H" & Lastrow)

My "H3:H" needs to be dynamic. I have an integer variable indicating the column that should be used in the range. That variable is a number like 8 or 5 or any value that it is updated with as my preceding operations loop through. I need to know how to define the above range with that number as a column indicator instead of letters like "H"

[p45cal]

The three lines beginning 'Cells' are all equivalent:
Cells(3, 8).AutoFill Destination:=Range("H3:H" & 30)
Cells(3, 8).AutoFill Destination:=Range(Cells(3, 8), Cells(30, 8))
r1 = 3
r2 = 30
colm1 = 8
'colm2=21
Cells(r1, colm1).AutoFill Destination:=Range(Cells(r1, colm1), Cells(r2, colm1))

[Ognox]

The three lines beginning 'Cells' are all equivalent:

Cells(3, 8).AutoFill Destination:=Range(Cells(3, 8), Cells(30, 8))



Thank you, p45cal! It wasn't as obvious to me :)