CODEWITHSUNDEEP
vbaexcelfor-loop
AleB
AleB

Reputation: 173

Excel VBA Calculate last row for each worksheet storing the value with a different name

I need to calculate the last row in every worksheet of my excel workbook and I'm actually doing that with the following lines of code:

With Sheets("Sheetname")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    LastRow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row
Else
    LastRow = 1
End If
End With

Is it possible to loop over every worksheet and store the LastRow values with a different name every time? I need all of them to be stored separately in my Macro.

Upvotes: 0

Views: 307

Answers (1)

gizlmo
gizlmo

Reputation: 1922

You can loop through Worksheets like this:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    'do something
Next ws

You could use a Scripting.Dictionary to store your values with ws.Name as Key and your .Cells.Find as Value. Place the code where the 'do something comment is.

Upvotes: 2

Related Questions