January 2020 – Nott's Notes

1. 统计所有sheet的总列数

Sub count_row()
Dim web As Object
Dim sht As Object
Dim path$
Dim arr
With Application.FileDialog(msoFileDialogOpen)
     .Filters.Add "Excel File", "*.xls,*xlsx", 1
     .InitialFileName = ThisWorkbook.FullName
     .Title = "选择要统计的EXCEL文件"
     .AllowMultiSelect = False
     If .Show = -1 Then
        path = .SelectedItems(1)
     Else
        Exit Sub
     End If
End With


Set web = Application.Workbooks.Open(path)
ReDim arr(1 To web.Worksheets.Count, 1 To 2)
Dim i: i = 1
For Each sht In web.Worksheets
    '获取每个工作表的行数（除标题行）
    arr(i, 1) = web.Name & "————" & sht.Name
    arr(i, 2) = sht.Cells(Rows.Count, 1).End(3).Row - 1
    i = i + 1
Next
ThisWorkbook.Worksheets(1).Cells.ClearContents
ThisWorkbook.Worksheets(1).Range("A1").Resize(1, 2) = Array("工作表名称", "行数（除标题行）")
ThisWorkbook.Worksheets(1).Range("A2").Resize(UBound(arr), 2) = arr
End Sub

Sub count_row()

Dim web As Object

Dim sht As Object

Dim path$

Dim arr

With Application.FileDialog(msoFileDialogOpen)

.Filters.Add "Excel File", "*.xls,*xlsx", 1

.InitialFileName = ThisWorkbook.FullName

.Title = "选择要统计的EXCEL文件"

.AllowMultiSelect = False

If .Show = -1 Then

path = .SelectedItems(1)

Else

Exit Sub

End If

End With

Set web = Application.Workbooks.Open(path)

ReDim arr(1 To web.Worksheets.Count, 1 To 2)

Dim i: i = 1

For Each sht In web.Worksheets

'获取每个工作表的行数（除标题行）

arr(i, 1) = web.Name & "————" & sht.Name

arr(i, 2) = sht.Cells(Rows.Count, 1).End(3).Row - 1

i = i + 1

ThisWorkbook.Worksheets(1).Cells.ClearContents

ThisWorkbook.Worksheets(1).Range("A1").Resize(1, 2) = Array("工作表名称", "行数（除标题行）")

ThisWorkbook.Worksheets(1).Range("A2").Resize(UBound(arr), 2) = arr

End Sub

2. foobar

M	T	W	T	F	S	S
« Dec				Feb »
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Month: January 2020

Excel 编程