Select a range using variables & COUNTA

Question

I would like to select all the contiguous cells in a sales report dump.

The report is a set number of columns (31). Although I would like to build a bit of variability into my code to accommodate a change in the number of columns.

The number of rows changes each week, sometimes less, sometimes more. It always starts at cell [A4].

I though of using COUNTA function to count used number of rows, then set that as a variable. Similar with rows.

I get

Run-time Error '1004': Method 'Range' of object'_Global failed

For me the key is to learn VBA using task I need getting done. I understand the logic behind my code. If some proposes a totally different code I might get lost.

But I am open minded.

Sub ReportArea()
    Dim numofrows As Integer
    Dim numofcols As Integer
    Dim mylastcell As String
    Dim myrange As Range
        
    Worksheets("Sheet1").Select
    numofrows = WorksheetFunction.CountA(Range("AE:AE"))
    numofcols = WorksheetFunction.CountA(Range("4:4"))
    Set myrange = Range(Cells(4, 1), Cells(numofrows, numofcols))
    Range(myrange).Select
End Sub

No... don't use COUNTA. see [This](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) to find the lastrow and then use it to create the range — Siddharth Rout, May 17 '15 at 09:29
@moshjosh `CountA` will give you only count of the not empty cells in range, it can't be used if you need to determine last row and last column — Vasily, May 18 '15 at 04:45
Don't edit a question 7-1/2 yrs later and complain the codes don't work for you. — Davesexcel, Oct 19 '22 at 13:22

Davesexcel · Answer 1 · 2022-10-19T13:09:00.710

Find last row and last column

Sub Sht1Rng()
    Dim ws As Worksheet
    Dim numofrows As Long
    Dim numofcols As Long
    Dim myrange As Range
    Set ws = Sheets("Sheet1")
    With ws
        numofrows = .Cells(.Rows.Count, "AE").End(xlUp).Row
        numofcols = .Cells(4, .Columns.Count).End(xlToLeft).Column
        Set myrange = .Range(.Cells(4, 1), .Cells(numofrows, numofcols))
    End With
    MsgBox myrange.Address

End Sub

You can also use this code.

Sub SelectLastCellInInSheet()
    Dim Rws As Long, Col As Integer, r As Range, fRng As Range
    Set r = Range("A1")
    Rws = Cells.Find(what:="*", after:=r, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Col = Cells.Find(what:="*", after:=r, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set fRng = Range(Cells(2, 1), Cells(Rws, Col))    ' range A2 to last cell on sheet
    fRng.Select    'or whatever you want to do with the range
End Sub

Hi @Davesexcel , when I tried your VBA with the data range: i.stack.imgur.com/ebVus.png it gave me a last row cell reference of: A$1$:F$4$ , so I'm unable to mark it as the solution. — Josh_BI_UK, Oct 18 '22 at 20:23

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Further to my above comment, is this what you are trying?

Sub ReportArea()
    Dim ws As Worksheet
    Dim Lrow As Long
    Dim myrange As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last row of COl AE. Change it to the relevant column
        Lrow = .Range("AE" & .Rows.Count).End(xlUp).Row

        Set myrange = .Range("A4:AE" & Lrow)

        With myrange
            '
            '~~> Do whatever you want to do with the range
            '
        End With
    End With
End Sub

Note: Also you don't need to select a range/worksheet. Work with objects. Interesting Read

edited May 23 '17 at 12:21

Community

1
1

answered May 17 '15 at 09:36

Siddharth Rout

147,039
17
206
250

Hi @SiddharthRout , when I tried your VBA with the data range: https://i.stack.imgur.com/ebVus.png it gave me a last row cell reference of: A$1$:Y$4$ , so I'm unable to mark it as the solution. – Josh_BI_UK Oct 18 '22 at 20:21
I have explained [HERE](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba/11169920#11169920) how to find the last cell. Feel free to take your pick. The method I have demonstrated above is to find the last row in Col AE. If you want the last row in the worksheet then use `.Find` as I have demostrated in that link. – Siddharth Rout Oct 19 '22 at 04:05

Vasily · Answer 3 · 2022-10-19T23:01:18.030

0

alternative solutions to already posted:

1:

Dim LRow&, LColumn&
Lrow = Sheets("SheetName").Cells.SpecialCells(xlCellTypeLastCell).Row
LColumn = Sheets("SheetName").Cells.SpecialCells(xlCellTypeLastCell).Column
MsgBox "Last Row is: " & Lrow & ", Last Column is: " & LColumn

2:

Dim x As Range
Set x = Range(Split(Sheets("SheetName").UsedRange.Address(0, 0), ":")(1))
MsgBox "Last Row is: " & x.Row & ", Last Column is: " & x.Column

output result

enter image description here

edited Oct 19 '22 at 23:01

answered May 18 '15 at 04:35

Vasily

5,707
3
19
34

Hi @Vasily , when I tried your VBA with the data range: i.stack.imgur.com/ebVus.png it gave me a last row message box of "Last Row is: 29, Last column is: 17" , so I'm unable to mark it as the solution. – Josh_BI_UK Oct 18 '22 at 20:26
1

Hi @Josh_BI_UK, I've tested once again based on screenshot provided in comments, for me it works fine, please see demo: https://i.stack.imgur.com/tXtIb.gif – Vasily Oct 19 '22 at 12:44

Select a range using variables & COUNTA

3 Answers3