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 to find the lastrow and then use it to create the range — Siddharth Rout
– Siddharth Rout, Commented May 17, 2015 at 9: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
– Vasily, Commented May 18, 2015 at 4:45
Don't edit a question 7-1/2 yrs later and complain the codes don't work for you. — Davesexcel
– Davesexcel, Commented Oct 19, 2022 at 13:22

Davesexcel · Accepted Answer · 2022-10-19 13:09:00Z

1

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

edited Oct 19, 2022 at 13:09

answered May 17, 2015 at 9:41

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Josh_BI_UK Over a year ago

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.

Davesexcel Over a year ago

The answers are based on your original question.

Community · Accepted Answer · 2017-05-23 12:21:55Z

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, 2017 at 12:21

CommunityBot

11 silver badge

answered May 17, 2015 at 9:36

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

2 Comments

Josh_BI_UK Over a year ago

Hi @SiddharthRout , when I tried your VBA with the data range: i.sstatic.net/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.

Siddharth Rout Over a year ago

I have explained HERE 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.

Vasily · Accepted Answer · 2022-10-19 23:01:18Z

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, 2022 at 23:01

answered May 18, 2015 at 4:35

Vasily

5,7923 gold badges22 silver badges36 bronze badges

2 Comments

Josh_BI_UK Over a year ago

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.

Vasily Over a year ago

Hi @Josh_BI_UK, I've tested once again based on screenshot provided in comments, for me it works fine, please see demo: i.sstatic.net/tXtIb.gif

Collectives™ on Stack Overflow

Select a range using variables & COUNTA

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related