My VBA method is causing Excel to crash - I cannot see the mistake

Question

EDIT: I may have spotted an issue as soon as posting it the myRange variables dont seem to be doing anything - so I'm feeling they were there from a method i was using ages ago and there decided to crop out

I'll remove the whole myRange variable and see what happens

 Set myRange = ActiveSheet.Range("1:1")
 Set myRange = ActiveSheet.Range("A:A")

EDIT 2: Ok so changing the numCols and numRows functions to only use

numCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
numRows = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row

They now return the correct row and Column numbers But now when I run selectBlock() it gives me runtime error 28 "Out of Stack Space"

Hello All, I've been writing code to be able to go through multiple sheets and copy the data across to a master workbook

Im coding this to work on any file depending what you pass to it - which has been fine

What im having problems with is the Functions I have made which find the last populated row for any sheet I pass to it

Sub test()

selectBlock().Select
End Sub


Function selectBlock() As Range

Dim row As Integer: row = numRows() 'Finds last populated row
Dim col As Integer: col = numCols() 'Finds last populated column

Set selectBlock() = Range("A2:" & Cells(row, col).Address)
'sets this area starting from cell A2 as the Range

End Function

Function numCols() As Integer

Dim myRange As Range
Set myRange = ActiveSheet.Range("1:1") 'Checks first row to see how many populated columns there are
numCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

End Function

Function numRows() As Integer
Dim myRange As Range
Set myRange = ActiveSheet.Range("A:A") 'Checks first columns to see how many populated rows there are
numRows = Range("A" & Rows.Count).End(xlUp).row

End Function

When I call the test Sub it causes Excel to hang then crash with no error code So i imagine im creating some kind of loop or critical error that isnt handled by excel very well

Any help with this would be really appreciated

I can also understand if how im going about it is incredibly stupid I used to code in Java and maybe im using techniques or pitfalls that I never got rid of - Im self taught at VBA like most and so never learnt official coding practices for VBA

Answer 1

Lot of things here

1. Fully qualify your cells
2. Use Long and not Integer when working with row and columns
3. Use error handling. This will avoid the Excel crashing.

Try this

Sub test()
    On Error GoTo Whoa
    selectBlock().Select
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Function selectBlock() As Range
    Dim row As Long: row = numRows() 'Finds last populated row
    Dim col As Long: col = numCols() 'Finds last populated column

    Set selectBlock = ActiveSheet.Range("A2:" & ActiveSheet.Cells(row, col).Address)
End Function

Function numCols() As Long
    numCols = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
End Function

Function numRows() As Long
    numRows = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).row
End Function

Answer 2

Replace

Set selectBlock() = Range("A2:" & Cells(row, col).Address)

to

Set selectBlock = Range("A2:" & Cells(row, col).Address)

it looks recursive :P

Answer 3

There are safer ways to find the LastRow and LastCol, I like the Find function.

See more detailed in my code's comments.

Code

Sub test()

Dim Rng As Range

Set Rng = selectBlock
Rng.Select '<-- Not sure why you need to Select ?

End Sub

'============================================================

Function selectBlock() As Range

Dim LastRow As Long
Dim LastCol As Long

LastRow = FindLastRow(ActiveSheet) 'Finds last populated row
LastCol = FindLastCol(ActiveSheet) 'Finds last populated column

Set selectBlock = Range(Cells(2, "A"), Cells(LastRow, LastCol))

End Function

'============================================================

Function FindLastCol(Sht As Worksheet) As Long

' This Function finds the last col in a worksheet, and returns the column number
Dim LastCell As Range

With Sht
    Set LastCell = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not LastCell Is Nothing Then
        FindLastCol = LastCell.Column
    Else
        MsgBox "Error! worksheet is empty", vbCritical
        End
    End If
End With

End Function

'============================================================

Function FindLastRow(Sht As Worksheet) As Long

' This Function finds the last row in a worksheet, and returns the row number

Dim LastCell As Range
With Sht
    Set LastCell = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not LastCell Is Nothing Then
        FindLastRow = LastCell.row
    Else
        MsgBox "Error! worksheet is empty", vbCritical
        End
    End If
End With

End Function