‘刪除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

轉載于:https://www.cnblogs.com/djcsch2001/archive/2011/08/19/2146143.html

總結

以上是生活随笔為你收集整理的VBA之EXCEL删除和设置单元格行高等的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。