Address property/function to return cell address as a pair/list/array of Integers

Question

I am in a dead end with a function() and a sub(). My code :

sub test()
Dim Firstrow, FirstCol As Integer
Workbooks("wb").Activate
Workbook(1).Worksheet(1).Select
FirstRow = 16
FirstCol = 20

LastCell = FindLastcell(FirstRow,FirstCol) 'LastCell is in "RiCj" format  
FirstCell = Cells(FirstRow, FirstCol).Address(ReferenceStyle:=xlR1C1) 'FirstCell is in "RiCj" format     
RngSelect = Range(FirstCell, LastCell).Select 'Range Method has failed. Obvious. See [1]
[more code to copy as text on Notepad the selection]
End Sub

Now my function :

Public Function FindLastCell(ByVal int1 As Integer, ByVal int2 As Integer) As Variant ' what kind of return dim shall I choose ?
' find first empty cell in a range and return its adress 

LastRow = Cells(Rows.Count, int1).End(xlUp).Row
LastCol = Cells(int2, Columns.Count).End(xlToLeft).Column
FindLastCell = Cells(LastRow, LastCol).Address(ReferenceStyle:=xlR1C1)
End Function

After having tried many variants, I can not get the desired result. Best would be in fact:

• My function shall return a list or array of Integers to use as cell adress in this style Cells(int1,int2)

• In my sub(), writing something like that:

  RngSelect = Range(Cells(i,j),Cells(k,l)).Select

I do not know how to achieve this whitout trigering in my function or in my sub an error.

[1] http://msdn.microsoft.com/en-us/library/office/ff838238.aspx If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation).

Thank you for help.

Answer 1

Few things...

1. When you declare your variables as Dim Firstrow, FirstCol As Integer in VBA then only the last variable will be declared as Interger. The first one will be declared as a variant. Use this instead. Dim Firstrow As Integer, FirstCol As Integer
2. When working with rows, avoid declaring the row variables as Integer. Declare then as Long. In Excel 2007+, declaring them as Integer might cause an Overflow Error.
3. Avoid the use of .Select/.Activate INTERESTING READ
4. Fully qualify your objects. For example the Cells object may give you an error or unexpected results.
5. Avoid the use of numbers in the Worksheet(1). Either use the actual names or the codenames of the sheet. This is to ensure that you work with the right sheet in case the sheets get shuffled.

Now to your query.

You don't need a function. See this

Dim wb As Workbook
Dim ws As Worksheet

Sub test()
    Dim Firstrow As Long, FirstCol As Long
    Dim FirstCell As String, LastCell As String
    Dim RngSelect As Range

    Firstrow = 16: FirstCol = 20

    '~~> Change this to the relevant path
    Set wb = Workbooks.Open("C:\wb.xlsx")
    Set ws = wb.Sheets("Sheet1")

    With ws
        FirstCell = Split(.Cells(, FirstCol).Address, "$")(1) & Firstrow

        LastRow = .Cells(.Rows.Count, FirstCol).End(xlUp).Row
        LastCol = .Cells(Firstrow, .Columns.Count).End(xlToLeft).Column

        LastCell = Split(.Cells(, LastCol).Address, "$")(1) & LastRow

        Set RngSelect = ws.Range(FirstCell & ":" & LastCell)

        Debug.Print RngSelect.Address
    End With

    '
    '[more code to copy as text on Notepad the selection]
    '
End Sub

However if you still need a function then see this.

Dim wb As Workbook
Dim ws As Worksheet

Sub test()
    Dim Firstrow As Long, FirstCol As Long
    Dim FirstCell As String, LastCell As String
    Dim RngSelect As Range

    Firstrow = 16: FirstCol = 20

    '~~> Change this to the relevant path
    Set wb = Workbooks.Open("C:\wb.xlsx")
    Set ws = wb.Sheets("Sheet1")

    With ws
        FirstCell = Split(.Cells(, FirstCol).Address, "$")(1) & Firstrow
        LastCell = FindLastCell(FirstCol, Firstrow)
        Set RngSelect = ws.Range(FirstCell & ":" & LastCell)

        Debug.Print RngSelect.Address
    End With

    '
    '[more code to copy as text on Notepad the selection]
    '
End Sub

Public Function FindLastCell(ByVal int1 As Long, ByVal int2 As Long) As String
    Dim LastRow As Long, LastCol As Long

    With ws
        LastRow = .Cells(.Rows.Count, int1).End(xlUp).Row
        LastCol = .Cells(int2, .Columns.Count).End(xlToLeft).Column
        FindLastCell = .Cells(LastRow, LastCol).Address
    End With
End Function