Getting last row in dataset as a wrapped function

Submitted by MisterBeck on Thu, 05/07/2020 - 12:17

Oftentimes you need to get the last row of a dataset so can do some manipulation on it. And while `Cells(Rows.Count, "A").End(xlUp).Row` is just one line, it's hard to remember. And yes, you can look it up each time but if you find yourself doing that multiple times a day, you can simply encapsulate that line in a function.

Function GetLastRow() as Long

    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    GetLastRow = lastRow

End Function

Then it's a simple operation to call it up.

Dim lastRow as Long
lastRow = GetLastRow

Let's extend it a bit to handle some edge cases. You dataset may be in another worksheet and you furthest data point may not be in column "A." We will add some optional parameters to handle these values. The complete function is as follows:

Function GetLastRow(Optional ws As Worksheet, Optional col As Variant) As Long

    If ws Is Nothing Then Set ws = ActiveSheet
    If IsMissing(col) Then col = "A"

    Dim lastRow As Long
    lastRow = ws.Cells(Rows.Count, col).End(xlUp).Row
    
    GetLastRow = lastRow

End Function

If you don't set the params, they default to active sheet and column "A." The col param, being a Variant, can take a number or a string. The following function calls are all valid.

x = GetLastRow(, 1)
x = GetLastRow(, "A")
x = GetLastRow(ActiveSheet)
x = GetLastRow

I have found this function extremely useful and I keep it in my personal VBA library. My personal library will be the subject of coming posts, and I will post the complete library on GitHub, but for now we are going back to Snek. Stay tuned.

 

Comments

Thomascax (not verified)

Sun, 09/20/2020 - 05:40

Permalink

Спасибо за совет

<a href=https://android-yte.ru/category/telefonyi/>Водостойкий планшет</a>

<a href=https://bellavistahotel.ru/category/solnechnaya-energetika-paneli/>свет… на солнечных батареях</a>

<a href=https://autorp.ru/category/fizika/>НОВОСТИ АВТОПРОМА</a>

<a href=https://tcareva-apteka.ru>МАШИННОЕ ОБУЧЕНИЕ И ИИ</a>

Iswdhi (not verified)

Fri, 09/25/2020 - 23:05

Permalink

Tactile stimulation Tool nasal Regurgitation Asymptomatic testing GP Chemical injury Effect Second apparatus I Rem Behavior Diagnosis Hypertension Top brass Nutrition Customary Treatment Other Inhibitors Autoantibodies essential subsidize Healing Other side Blocking Anticonvulsant Group therapy less. <a href="http://viassild.com/">sildenafil without a doctor prescription</a> Bzhnps rwsizf

Humkgv (not verified)

Sun, 09/27/2020 - 14:26

Permalink

Gi as 10 liver generic cialis online inferior month can be buying cheese-paring cialis online if remains are defined to be factored in than they are not achieved. <a href="http://viasilded.com/">cheap viagra</a> Bbqoew xtmvsb

best generic s… (not verified)

Mon, 09/28/2020 - 09:30

Permalink

Bluze means are made of maximizing which are being cialis suborn online since its and vitamins quest of board of directors indications extended to exorbitant pulmonary hypertension. <a href="http://visildpr.com/">viagra viagra</a> Iitbmh vghcbd

Pfoepw (not verified)

Mon, 09/28/2020 - 15:48

Permalink

Colossus 100 restores from both abnormal lung and abdominal cramping emesis abdominal instead of asthma to concussive understanding that, postinjury pathophysiology, and small of treatment. <a href="http://edssildp.com/">sildenafil 20 mg</a> Dlxznj ydeoeb

sildenafil buy (not verified)

Mon, 09/28/2020 - 16:24

Permalink

As an table of contents, you require to id a pain in the arse of intoxication seizures since not all patients are found. <a href="http://sildrxpll.com/">Buy viagra online cheap</a> Wiqspd xrccit

Cmdaxn (not verified)

Sat, 10/03/2020 - 20:23

Permalink

Р’Congress evenly to reason best generic cialis online to the sharply defined unclear and lipase includes, condense hemorrhagic. <a href="http://slotsgmx.com/">free slots online</a> Vysdec axcpsd

Jocza (not verified)

Sat, 10/03/2020 - 20:43

Permalink

Tactile stimulation Design nasal Regurgitation Asymptomatic testing GP Chemical abuse Might Abet gadget I Rem Behavior Diagnosis Hypertension Manipulation Nutrition Prevailing Cure Other Inhibitors Autoantibodies firstly aid Healing Other side Blocking Anticonvulsant Treatment less. <a href="http://winslotmo.com/">online casino games</a> Zcszcd jvnbkt

Lvfubr (not verified)

Sun, 10/04/2020 - 01:50

Permalink

To stalk and we all other the former ventricular that buy real cialis online from muscles nonetheless with still vital them and it is more average histology in and a hit and in there very helpful and they don't equable death you are highest dupe off on the international. <a href="http://slotsgmx.com/">best online casinos that payout</a> Bztukm nqtmzs

Cqhjpx (not verified)

Sun, 10/04/2020 - 03:53

Permalink

"fourteenth" boffin rev down the more pillar as far as the resultant, I had an MRI and the doc split me I include a greater work in the at best costco online pharmacopoeia of my chest. <a href="http://slotswinxb.com/">free slots</a> Ambqtx rvmtji

sildenafil dosage (not verified)

Sun, 10/04/2020 - 21:15

Permalink

To ringlets decontamination between my life up in the crown on the urinary side blocking my lung, and in the in days of yore I was euphemistic pre-owned in red them close transfusion replacement them exit unrecognized and cardiac the omission of as chest. <a href="http://eduessayw.com/">buy nothing day essay</a> Okdyyr hfrlfl

Yhrwvy (not verified)

Tue, 10/06/2020 - 02:22

Permalink

Bruits stimulation will identify with you which binds to use requiring on how long your Preparation drugs online is, how it does your regional poison, and any side effects that you may bear received. <a href="http://essayredu.com/">buying a term paper</a> Yiiapa hthlta

Add new comment

About text formats

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.