Pointers in VBA

Question

Are there something like pointers in VBA?

I'm trying to make an excel macro, and want a simple variable to keep track of column count. wst.UsedRange.Columns.Count takes up a lot of space every time I need it, and I'd like to just replace that with a simple name like last_col.

"wst.UsedRange.Columns.Count takes up a lot of space": what do you mean? — assylias, Feb 27 '12 at 16:54
@assylias Perhaps it has a lot of characters. It takes up a lot of space on the screen when Mark types it. — GeoffDS, Feb 27 '12 at 17:26
@Graphth That would make sense although it is not the first thing that came to mind! — assylias, Feb 27 '12 at 17:33
Should Mark be using `wst.UsedRange.Columns.Count` as a reliable value that can be referenced as necessary? I always maintain my own counts. See [Excel - Getting the actual usedrange](http://stackoverflow.com/q/7423022/973283) for some excellent information on the limitations of `UserRange`. — Tony Dallimore, Feb 27 '12 at 17:42
Yes, it's the character count that I was referring to as "space". — Mark, Feb 29 '12 at 14:27
BTW, the files I'm working with are large tables of data that don't have gaps (unless something went wrong creating them), so I haven't had many issues with UsedRange. — Mark, Feb 29 '12 at 14:32
Issues with UsedRange are common. If you are only doing something very straightforward, it usually works but there are cases where it doesn't. In particular there are problems when rows or columns are hidden. See the [good discussion here](http://stackoverflow.com/questions/35712424/how-to-find-the-true-last-cell-in-any-worksheet). It includes a code snippet that reliably gets the true last cell. I.e. the cell at the intersection of the rightmost column with values/formulas and the and highest numbered row that contains values/formulas. — Neil Dunlop, Oct 07 '16 at 17:10

score 4 · Accepted Answer · answered Feb 27 '12 at 16:56

There are but not in a way useful in your situation (or much else beyond special cases), why not

public function ccount() as long
   ccount = wst.UsedRange.Columns.Count
end function

or set a global variable reference to wst.UsedRange.Columns and read var.count as needed.

Jerry Beaucaire · Answer 2 · 2012-03-02T01:41:54.090

I'm no fan of UsedRange in most instances. If you were to create a Function to be used over and over by your other macros, I would lean toward this syntax which finds the last column with an actual value in it.

Private Function LastCOL(ws As Worksheet) As Long
    With ws
        LastCOL = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), _
           SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End With
End Function

Private Function LastROW(ws As Worksheet) As Long
    With ws
        LastROW = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), _
           SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
    End With
End Function

Then your other macros can call it by simply feeding the macro a sheet reference:

Sub RowTest()
    On Error Resume Next       'in case the sheet is empty
    LR = LastROW(ActiveSheet)
    Debug.Print LR
End Sub

This was basic demonstration, you can employ your favored method of error handling. This is simple enough that we can just ignore the error and get the ZERO back, that's the correct answer on a blank sheet, yes? Added above. — Jerry Beaucaire, Mar 02 '12 at 01:44

Pointers in VBA

2 Answers2