Geek Answers Handbook

What is the best way to get the last nonblank sheet in Excel (VBA)

I'm trying to write a vba macro for tha group

  • there is one book in which they create new worksheets daily, and also have
  • Sheet 1, sheet 2 and sheet 3 at the end of their long list of sheets.

I need to create a link to an external cell in a new column in another book where this information is summarized.

So, I need to know how to get the last non-empty sheet so that I can capture this data and put it accordingly in the summary.

+4
source share
2 answers

This function works through the sheets from right to left until it finds a non-empty sheet and returns its name

Function GetLastNonEmptySheetName() As String Dim i As Long For i = Worksheets.Count To 1 Step -1 If Sheets(i).UsedRange.Cells.Count > 1 Then GetLastNonEmptySheetName = Sheets(i).Name Exit Function End If Next i End Function
+6
source

In the above method, a single-cell recording sheet will be ignored, although this may seem like a cripple, a Find looking for a non-empty cell will give more confidence.

The xlFormulas argument in the Find method will find hidden cells (but not filtered cells), while xlValues will not.

 Sub FindLastSht() Dim lngCnt As Long Dim rng1 As Range Dim strSht As String With ActiveWorkbook For lngCnt = .Worksheets.Count To 1 Step -1 Set rng1 = .Sheets(lngCnt).Cells.Find("*", , xlFormulas) If Not rng1 Is Nothing Then strSht = .Sheets(lngCnt).Name Exit For End If Next lngCnt If Len(strSht) > 0 Then MsgBox "Last used sheet in " & .Name & " is " & strSht Else MsgBox "No data is contained in " & .Name End If End With End Sub
+1
source

More articles:


All Articles