VBA特殊单元格定位、单元格信息表示

'1 已使用的单元格区域

Sub d1()

Sheets('sheet2').UsedRange.Select

'wb.Sheets(1).Range('a1:a10').Copy Range('i1')

End Sub

'2 某单元格所在的单元格区域

Sub d2()

Range('b8').CurrentRegion.Select

End Sub

'3 两个单元格区域共同的区域

Sub d3()

Intersect(Columns('b:c'), Rows('3:5')).Select

End Sub

'4 调用定位条件选取特殊单元格

Sub d4()

Range('A1:A6').SpecialCells(xlCellTypeBlanks).Select

End Sub

'5 端点单元格

Sub d5()

Range('a65536').End(xlUp).Offset(1, 0) = 1000

End Sub

Sub d6()

Range(Range('b6'), Range('b6').End(xlToRight)).Select

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'1 单元格的值

Sub x1()

Range('b10') = Range('c2').Value

Range('b11') = Range('c2').Text

Range('c10') = ''' & Range('I3').Formula

End Sub

'2 单元格的地址

Sub x2()

With Range('b2').CurrentRegion

[b12] = .Address

[c12] = .Address(0, 0)

[d12] = .Address(1, 0)

[e12] = .Address(0, 1)

[f12] = .Address(1, 1)

End With

End Sub

'3 单元格的行列信息

Sub x3()

With Range('b2').CurrentRegion

[b13] = .Row

[b14] = .Rows.Count

[b15] = .Column

[b16] = .Columns.Count

[b17] = .Range('a1').Address

End With

End Sub

'4、单元格的格式信息

Sub x4()

With Range('b2')

[b19] = .Font.Size

[b20] = .Font.ColorIndex

[b21] = .Interior.ColorIndex

[b22] = .Borders.LineStyle

End With

End Sub

'5、单元格批注信息

Sub x5()

[B24] = Range('I2').Comment.Text

End Sub

'6 单元格的位置信息

Sub x6()

With Range('b3')

[b26] = .Top

[b27] = .Left

[b28] = .Height

[b29] = .Width

End With

End Sub

'7 单元格的上级信息

Sub x7()

With Range('b3')

[b31] = .Parent.Name

[b32] = .Parent.Parent.Name

End With

End Sub

'8 内容判断

Sub x8()

With Range('i3')

[b34] = .HasFormula

[b35] = .Hyperlinks.Count

End With

End Sub