0

There's probably a simple fix here, but my defined range is not being picked up.

I have an evolving set of data that will refresh. When I run the macro, I want to set a range for the values in the last 5 rows of the table to check win / loss (in column H) for a Count If function. But when I run the VBA trouble shoot command, a range value never gets set and my formula fails for Run-time error 1004. I've tried with both Selection.Offset and ActiveCell.Offset.

I feel like I'm making a basic mistake, but can narrow it down or easily find examples here to replicate

Dim fivegame As Range
Range("H1").Select
Selection.End(xlDown).Select
Set fivegame = Range(Selection.Offset(-4, 0), Selection)
AHeyne
  • 3,377
  • 2
  • 11
  • 16
dcallaghan3431
  • 9
  • 2
  • 1
    The code above will only work if `Selection.End(xlDown).Select` hits `Range("H5")` at the minimum otherwise `Selection.Offset(-4, 0)` is outside the worksheet. And, of course, it is not neccessary to use `Select`, see [How to avoid Select](https://stackoverflow.com/a/10717999/6600940). And I am afraid `Selection.End(xlDown).Select`will not hit the last filled row in column H unless there are no gaps. [How to find last used row?](https://stackoverflow.com/a/11169920/6600940). – Storax Feb 27 '21 at 08:07
  • Does this answer your question? [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) – Storax Feb 27 '21 at 08:09

2 Answers2

0

Where do you get the error? Does this work?

Dim fivegame As Range

   Set fivegame = Range("H" & Rows.Count).End(xlUp).Offset(-4).Resize(5)
norie
  • 9,609
  • 2
  • 11
  • 18
  • Hi Norie - I don't get the error until the Countifs formula, but the above worked great. Not sure what the issue was originally in mine, but this was very helpful. Thank you so much! – dcallaghan3431 Feb 28 '21 at 03:18
0

Create a Reference to the Last Cells in a Column

  • The function refLastCellsInColumn will return a reference to the range consisting of the last cells in a column i.e. the number of consecutive cells above the last non-empty cell (incl.) defined by the parameter (number) supplied to the NumberOfCells argument. The function will return Nothing if the reference cannot be created (e.g. the range is supposed to start with a cell above the first cell...).

The Code

Option Explicit

Sub HowToUseItInYourProcedure()
    Dim fivegame As Range
    Set fivegame = refLastCellsInColumn(Range("H1"), 5)
End Sub

Function refLastCellsInColumn( _
    ByVal FirstCell As Range, _
    Optional ByVal NumberOfCells As Long = 1, _
    Optional ByVal allowLessCells As Boolean = False) _
As Range
    If Not FirstCell Is Nothing And NumberOfCells > 0 Then
        With FirstCell
            Dim rg As Range
            Set rg = .Resize(.Worksheet.Rows.Count - .Row + 1) _
                .Find("*", , xlFormulas, , , xlPrevious)
            If Not rg Is Nothing Then
                If rg.Row - .Row >= NumberOfCells - 1 Then
                    Set refLastCellsInColumn = _
                        rg.Offset(1 - NumberOfCells).Resize(NumberOfCells)
                Else
                    If allowLessCells Then
                        Set refLastCellsInColumn = .Resize(rg.Row - .Row + 1)
                    End If
                End If
            End If
        End With
    End If
End Function

Sub refLastCellsInColumnTEST()
    Const FirstCellAddress As String = "H1"
    Const NumberOfCells As Long = 2000000
    ' Define First Cell Range.
    Dim cel As Range: Set cel = Range("H1")
    ' Define Last Cells in column.
    Dim rg As Range: Set rg = refLastCellsInColumn(cel, NumberOfCells, True)
    ' Test.
    If Not rg Is Nothing Then
        Debug.Print rg.Address
    Else
        Debug.Print "Nope"
    End If
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28