Excel VBA - Select a range using variables & COUNTA

Question

Hi Staked VBA Kings & Queens, I'm trying to learn Excel VBA. A simple task I would like to do is select all the contagious cells in a report dump I get from sales. Simple i'm sure, but I am a total beginner at VBA.

Ok Report Info:

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 column numbers.

The report grows by number of rows each week, some times less, sometimes more. But 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.

This is what I came up with, although I get a "Run-time Error '1004': Method 'Range' of object'_Global failed... can anyone help me out".

For me the key is to learn VBA using task I need getting done. I understand the logic behind my code, but not exactly the write way to write it. 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

P.S I did try read slimier trends but only got confused as the solution where very involved.

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 Ivoyzha, May 18 '15 at 04:45

score 1 · Answer 1 · answered May 17 '15 at 09:41

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

Bookmark this page http://www.xlorate.com/selection-codes.html

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

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

score 0 · Answer 3 · answered May 18 '15 at 04:35

also one additional option from my side to already posted variants\

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

output result

enter image description here

Excel VBA - Select a range using variables & COUNTA

3 Answers3