获取有数据的单元格
Set rng = ws.UsedRange
Get all the cells with data.range更改
Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _
rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))
ws.Cells
获得Range的第一个单元格的坐标
rng.SpecialCells(xlCellTypeLastCell).Offset(-1,0)
获得最后一个单元格的坐标,且行数减1
- 简化格式后的完整程序
Sub FormatAndChart( )
' AutoFormats and Charts all of the worksheets in a workbook.
' Designed to work with Sales Data tables.
' 5/28/04 by Jeff Webb '
Dim rng As Range, ws As Worksheet
' Repeats actions for all Worksheets in the workbook.
For Each ws In Worksheets
' Get the cells with data in them.
Set rng = ws.UsedRange
' Apply AutoFormat
rng.AutoFormat Format:=xlRangeAutoFormatSimple
' Omit the Total row from the range.
Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _
rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))
' Create a chart.
Charts.Add
' Set chart properties.
ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
' Insert the chart on the worksheet.
ActiveChart.Location Where:=xlLocationAsObject, Name:=ws.Name Next
End Sub
- 显示message box
Sub ShowMessage( )
Dim x As Integer
x = Sheets.Count
MsgBox "This workbook contains " & x & " sheets."
End Sub
- 写入单元格
Sub ChangeRange( )
Dim x As Double
x = InputBox("Enter a number.")
Range("J5") = x ^ (1 / 3)
End Sub
- 公共函数 module
Public Function CubeRoot(x As Double) As Double
CubeRoot = x ^ (1 / 3)
End Function
- 自动填充
Range("A2").AutoFill Destination:=Range("A2:A26"), Type:=xlFillDefault
- 对于大于平均值的数用绿色背景标记
Selection.FormatConditions.AddAboveAverage
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).AboveBelow = xlAboveAverage
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
MsgBox "Added an Above Average Conditional Format to Melanie's data. Press F9 to update values.", vbInformation
End Sub