求某个sheet中的有数据的最后一行的行号，最后一列的列数
Excel & VBa: find last row, column, cell in an Excel (work)sheet
The following page contains some usefull (general) vba code that can be used to find the last row, column and/or cell in an Excel (work)sheet.
原文来自：http://www.nijbo.net/index.php?option=com_content&task=view&id=14&Itemid=32
1 Find the last used cell, before a blank in a Column:
2 Sub LastCellBeforeBlankInColumn()
3 Range("A1").End(xldown).Select
4 End Sub
5
6 Find the very last used cell in a Column:
7 Sub LastCellInColumn()
8 Range("A65536").End(xlup).Select
9 End Sub
10
11 Find the very last used cell in a Column:
12 Sub LastCellInColumn()
13 Range("A65536").End(xlup).Select
14 End Sub
15
16 Find the last cell, before a blank in a Row:
17 Sub LastCellBeforeBlankInRow()
18 Range("A1").End(xlToRight).Select
19 End Sub
20
21 Find the very last used cell in a Row:
22 Sub LastCellInRow()
23 Range("IV1").End(xlToLeft).Select
24 End Sub
25
26 Find the very last used cell on a Worksheet:
27 Sub Demo()
28 Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
29 End Sub
30
31
32 Find the last used Row on a Worksheet:
33 Sub FindLastRow()
34 Dim LastRow As Long
35
36 If WorksheetFunction.CountA(Cells) > 0 Then
37 'Search for any entry, by searching backwards by Rows.
38 LastRow = Cells.Find(What:="*", After:=[A1], _
39 SearchOrder:=xlByRows, _
40 SearchDirection:=xlPrevious).Row
41 MsgBox LastRow
42 End If
43 End Sub
44
45 Find the last used Column on a Worksheet:
46 Sub FindLastColumn()
47 Dim LastColumn As Integer
48 If WorksheetFunction.CountA(Cells) > 0 Then
49 'Search for any entry, by searching backwards by Columns.
50 LastColumn = Cells.Find(What:="*", After:=[A1], _
51 SearchOrder:=xlByColumns, _
52 SearchDirection:=xlPrevious).Column
53 MsgBox LastColumn
54 End If
55 End Sub
56
57 Find the last used Cell on a Worksheet:
58 Private Sub FindLastCell()
59 Dim LastColumn As Integer
60 Dim LastRow As Long
61 Dim LastCell As Range
62 If WorksheetFunction.CountA(Cells) > 0 Then
63 'Search for any entry, by searching backwards by Rows.
64 LastRow = Cells.Find(What:="*", After:=[A1], _
65 SearchOrder:=xlByRows, _
66 SearchDirection:=xlPrevious).Row
67 'Search for any entry, by searching backwards by Columns.
68 LastColumn = Cells.Find(What:="*", After:=[A1], _
69 SearchOrder:=xlByColumns, _
70 SearchDirection:=xlPrevious).Column
71 MsgBox Cells(LastRow, LastColumn).Address
72 End If
73 End Sub
74
75 Add a row at each change in a column.
76 Assume you have a long list of data and you want to insert a row at each change. While you could use a simple Loop this method is much faster. The Data must be sorted!
77
78 Option Explicit
79
80 Private Sub InsertRowAtEachChange()
81 Dim objRange As Excel.Range
82
83 ''' On error goto the error handler defined in the lower part of this function.
84 On Error GoTo ErrHandler
85
86 ''' Ensure an entire Column is selected
87 If Selection.Cells.Count <> 65536 Then
88 Call MsgBox("You must select an entire column", vbCritical)
89 End
90 End If
91
92 '''Set a range variable to all data in selected column
93 Set objRange = Range(Selection.Cells(2, 1), Selection.Cells(65536, 1).End(xlUp))
94
95 ''' Add a column for formulas
96 With objRange
97 .EntireColumn.Insert
98 .Offset(0, -1).FormulaR1C1 = "=IF(AND(NOT(ISNA(R[-1]C))," & _
99 "R[-1]C[1]<>RC[1]),0,"""")"
100 ''' Convert to values
101 .Offset(0, -1) = .Offset(0, -1).Value
102 ''' Set variable to 0
103 Set objRange = .Offset(0, -1).SpecialCells(xlCellTypeConstants, xlNumbers)
104 End With
105
106 ''' Add a row at each 0
107 If WorksheetFunction.CountIf(objRange, 0) > 0 Then
108 Call objRange.EntireRow.Insert
109 End If
110
111 ''' Reset variable for next formulas
112 Set objRange = Range(Selection.Cells(2, 1), Selection.Cells(65536, 1).End(xlUp))
113
114 ''' Add the formula to add 0
115 objRange.FormulaR1C1 = "=IF(OR(RC[1]="""",R[-1]C[1]=""""),""""," & _
116 "IF(RC[1]<>R[-1]C[1],0))"
117 ''' Convert to values
118 objRange = objRange.Value
119
120 ''' Set variable to 0 cells if any
121 If WorksheetFunction.CountIf(objRange, 0) > 0 Then
122 Set objRange = objRange.SpecialCells(xlCellTypeConstants, xlNumbers)
123 ''' Add a row at each 0
124 objRange.EntireRow.Insert
125 End If
126
127 ''' Delete added Column
128 Call objRange.Columns(1).EntireColumn.Delete
129
130 ''' Remove an instance
131 Set objRange = Nothing
132
133 ''' Exit the Sub
134 Exit Sub
135
136 ''' Define the subfunction for errorhandling.
137 ErrHandler:
138 ''' Error.
139 '''Call gobjLogFile.Error("ThisWorkbook, function : InsertRowAtEachChange", _
140 "Description goes here", Err.Number, Err.Description)
141
142 ''' Resume anyway.
143 Resume Next
144
145 End Sub
146
147
