w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Address property/function to return cell address as a pair/list/array of Integers

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




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.