VBA, MS Access MS Access в примерах

MS Excel - Подсчёт заполненных строк в листе и в конкретной колонке

Sub test000()
Dim sVal$, lVal&, sListName$
    sListName = "Потребность для загрузки"
' кол-во  заполненных строк на листе
    lVal = ActiveWorkbook.Worksheets(sListName).UsedRange.Rows.Count
    sVal = "На листе: """ & sListName & vbCrLf & _
        "Заполнено всего " & lVal & " строк." & vbCrLf
' кол-во заполненных строк в колонке "E"
    lVal = ActiveWorkbook.Worksheets(sListName).Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row
    sVal = sVal & "А конкретно в колонке ""E"", " & vbCrLf & _
        "заполнено всего: " & lVal & " rows."
    MsgBox sVal, vbInformation, "Кол-во строк"
End Sub

Dim objExcelApp As Object
Dim objWorkbook As Object
Dim objWorksheet As Object
Dim strPath As String

    strPath = "d:\Downloads\Book1.xlsx"
    Set objExcelApp = CreateObject("Excel.Application")
    'Set objExcelApp = GetObject(, "Excel.Application")
    Set objWorkbook = objExcelApp.Workbooks.Open(strPath)
    'Set objWorkbook = objExcelApp.ActiveWorkbook
    Set objWorksheet = objWorkbook.Worksheets(1)

'Заполненные строки на листе
    Debug.Print objWorksheet.UsedRange.Rows.Count

    On Error Resume Next
    Set objWorkbook = Nothing
    Set objExcelApp = Nothing

По материалам: https://www.accessforums.net/showthread.php?t=88228

I now use this, that I created instead of trying to remember the syntax all the time.

Function GetLastRow(pstrSheet As String, Optional pstrColumn As String) As Integer
' Return last use row for sheet and column passed in
Dim iLastRow As Integer
Dim sht As Worksheet

Set sht = Sheets(pstrSheet)
If IsNull(pstrColumn) Then pstrColumn = "A"

iLastRow = sht.Cells(ActiveSheet.Rows.Count, pstrColumn).End(xlUp).Row
GetLastRow = iLastRow
Set sht = Nothing

End Function

    Change integer to Long if you have stupidly large sheets.

Назад ToTop
L.E. 16.10.2023