0

I use this method of finding the last row of data, and this particular method works for my purpose:

Range("A" & Rows.Count).End(xlUp).Row

I also made a form button that links to a macro called Button1_Click(). At the click of this button I'd like to get the last row of data for 3 Excel sheets stored into variables LastRow1, LastRow2, and LastRow3. So assuming I have 3 sheets, and the last row of data on Sheet1 is 5, Sheet2 is 13, and Sheet3 is 84, LastRow1 should equal 5, LastRow2 should equal 13, and LastRow3 should equal 84.

Sub Button1_Click()
       LastRow1 = LastRow
End Sub

Function LastRow() As Long
       Range("A" & Rows.Count).End(xlUp).Row
End Function

I don't know how to "pass" the name of Sheet1 into the LastRow function and have it spit back the last row so that I can assign the row number to variables LastRow1, LastRow2, and LastRow3.

MrPatterns
  • 4,184
  • 27
  • 65
  • 85

1 Answers1

1

Pass the sheet name as a parameter to the LastRow() function then construct the range using the sheet name. Notice the exclamation mark front of the A.

Function LastRow(sheet as String) As Long
   LastRow = Range(sheet & "!A" & Rows.Count).End(xlUp).Row
End Function

Then your Button1_Click() routine becomes

Sub Button1_Click()
   LastRow1 = LastRow("Sheet1")
   LastRow2 = LastRow("Sheet2")
   LastRow3 = LastRow("Sheet3")
End Sub
drew
  • 2,371
  • 2
  • 19
  • 27
  • I get a compile error that highlights .Row in Function LastRow and says Invalid use of property... – MrPatterns Nov 22 '13 at 16:17
  • Try `Range("'" & sheet & "'!A" & Rows.Count).End(xlUp).Row` – ApplePie Nov 22 '13 at 16:23
  • Do you need that Row? If you remove it, the value of the last cell in column A gets returned – drew Nov 22 '13 at 16:36
  • Just spotted it - the LastRow function was not returning the value. I've just updated the answer – drew Nov 22 '13 at 16:41
  • I got run-time error '1004': Method 'Range' of object '_Global' failed. Is it because I've placed the code in Module 1? – MrPatterns Nov 22 '13 at 16:50
  • I believe it should be `sheets(sheet).Range("A" & .Rows.Count).End(xlUp).Row` – gtwebb Nov 22 '13 at 17:21
  • I figured out that drew's answer only works if you do NOT rename the sheets to names of your own choosing. If you leave it at defaults, Sheet1, Sheet2, etc. the code will work. – MrPatterns Nov 22 '13 at 19:07
  • Yes there are multiple ways of referring to sheets in vba. The code above takes the actual sheet names in excel so if anyone changes them the code won't work. You can also refer to them as to which sheet number they are or have set names in vba. A quick search on how to reference sheets in vba should bring up all the different methods – gtwebb Nov 22 '13 at 19:44