‘删除Sheet1上的单元格区域A1:D10,并将其余单元格左移以填补被删除单元格的位置
Sheet1.Range(“A1:D10”).Delete Shift:=xlShiftToLeft
‘删除指定行
Range(“1:1”).Delete
‘删除指定列
Columns(5).Delete
‘删除当前行
ActiveCell.EntireRow.Delete
‘删除工作表中的重复行
Sub DeleteDupes(strSheetName as String,strColLetter as string)
Dim strColRange as String
Dim rngCurrentCell as Range
Dim rngNextCell as Range
strColRange=strColLetter & “1”
Worksheets(strSheetName).Range(strColRange).Sort Key1:=Worksheets(strSheetName).Range(strColRange)
Set rngCurrentCell=Worksheets(strSheetName).Range(strColRange)
Do While Not IsEmpty(rngCurrentCell)
Set rngNextCell=rngCurrentCell.Offset(1,0)
If rngNextCell.Value=rngCurrentCell.Value Then
rngCurrentCell.EntireRow.Delete
End If
Set rngCurrentCell=rngNextCell
Loop
End Sub
‘删除自定义数字格式
Sub DeleteNumberFormat()
MsgBox “从当前工作簿中删除000-00-0000的数字格式”
ActiveWorkbook.DeleteNumberFormat(“000-00-0000”)
End Sub
‘清除内容
Sub ClearContents()
Selection.SpecialCells(xlCellTypeConstants,23).Select
Selection.ClearContents
End Sub
‘清除选定单元格的批注
Sub ClearComments()
Selection.SpecialCells(xlCellTypeComments,23).Select
Selection.ClearComments
End Sub
‘单元格的插入
‘在数据表上的单元格区域A1:C5中插入新单元格，并将该位置上原来的单元格向下移动
Sheet1.Range(“A1:C5”).Insert Shift:=xlShiftDown
‘在当前单元格上方插入行
Sub InsertRow()
Dim rRow as Long
MsgBox “在当前单元格上方插入一行”
rRow=Selection.Row
ActiveSheet.Rows(Row).Insert
End Sub
‘在当前单元格所在列插入列
Sub InsertColumn()
Dim cColumn As Long
MsgBox “在当前单元格所在列的左边插入一列”
cColumn=selection.Column
ActiveSheet.Columns(cColumn).Insert
End Sub
‘在当前单元格上方插入多行
Sub InsertManyRow()
MsgBox “在当前单元格所在行上插入三行”
Dim rRow as long ,I as long
For i=1 to 3
rRow=Selection.Row
ActvieSheet.Rows(rRow).Insert
Next i
End Sub
‘在活动工作表的第1-3行处插入三行
Sub InsertRows()
MsgBox “在当前单元格所在行上方插入三行”
ActiveSheet.Rows(“1:3”).Insert
End Sub
‘隐藏当前单元格所在的行
Sub HideRow()
Dim iRow as Long
MsgBox “隐藏当前单元格所在的行”
iRow=ActiveCell.Row
ActiveSheet.Rows(iRow).Hidden=True
MsgBox “取消隐藏”
ActiveSheet.Rows(iRow).Hidden=False
End Sub
‘隐藏当前单元格所在的列
Sub HideColumn()
Dim iColumn as Long
MsgBox “隐藏当前单元格所在的列”
iColumn =ActiveCell.Column
ActiveSheet.Rows(iColumn).Hidden=True
MsgBox “取消隐藏”
ActiveSheet.Rows(iColumn).Hidden=False
End Sub
‘设置当前所在单元格的行高
Sub SetRowHeight()
MsgBox “将当前单元格所在的行高设置为25”
Dim rRow As Long,iRow As Long
rRow=ActiveCell.Row
iRow=ActiveSheet.Rows(rRow).RowHeight
ActiveSheet.Rows(rRow).RowHeight=25
MsgBox “恢复到原来的行高”
ActiveSheet.Rows(rRow).RowHeight=iRow
End Sub
‘设置最合适的行高和列宽
Sub AutoFitRowCol()
Rows(“9:9”).Select
Selection.Rows.AutoFit
Columns(“B:D”).Select
Selection.Columns.AutoFit
End Sub
‘设置当前单元格的列宽
Sub SetColumnWidth()
MsgBox “将当前单元格所在列的列宽设置为20”
Dim cColumn As Long,iColumn As Long
cColumn=ActiveCell.Column
iColumn=ActiveSheet.Columns(cColumn).ColumnWidth
ActiveSheet.Columns(cColumn).ColumnWidth=20
MsgBox “恢复至原来的列宽”
ActiveSheet.Columns(cColumn).ColumnWidth= iColumn
End Sub
‘锁定A1:A5单元格
Range(“A1:A5”).Locked=True
‘接除对Sheet1中A1:G37区域单元格的锁定，以便当该工作表受保护时也可以对这些单元格进行修改
Sub UnlockedCell()
Worksheets(“Sheet1”).Range(“A1:G37”).Locked=False
Worksheets(“Sheet1”).Protect
End Sub
‘自动对有内容的单个单元格锁定，对没有内容的单个单元格解除锁定
Private Sub Worksheet_SelectionChange(ByVal Targe As Range)
On Error Resume Next
If Targe.Cells.Count=1 Then
‘如果目标单元格为空
If Targe=”” Then
‘解除工作表的保护
Me.Unprotect(“password”)
Target.Locked=False
Me.Protect(“password”)
Else
Me.Unportect(“password”)
‘设置单元格的锁定
Target.Locked=True
Me.Protect(“password”)
End If
Else
MsgBox “请选择一个单元格”,vbInformation
ActiveCell.Select
End If
End Sub
