我有以下代码来根据标题选择列。
Dim rng1 As Range
Set rng1 = Range(Range("A1:Z1").Find("Name"), Range("A1:Z1").Find("Name").End(xlDown))
当尝试使用此范围并在图表上设置 XValue 时
ActiveChart.SeriesCollection(5).XValues = rng1
我看到标题也出现在列表中。
想知道一种根据标题选择列然后从中删除标题元素的方法。
Try this
Set rng1 = Range( _
Range("A1:Z1").Find("Name").Offset(1), _
Range("A1:Z1").Find("Name").Offset(1).End(xlDown))
不过请注意。xlDown
如果从第二行开始没有数据,可能会给您带来意想不到的结果。如果找不到名称,您所采取的方法也会给您带来错误。
话虽如此,这是一个替代方案
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long
Dim aCell As Range, rng1 As Range
'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Find the cell which has the name
Set aCell = .Range("A1:Z1").Find("Name")
'~~> If the cell is found
If Not aCell Is Nothing Then
'~~> Get the last row in that column and check if the last row is > 1
lRow = .Range(Split(.Cells(, aCell.Column).Address, "$")(1) & .Rows.Count).End(xlUp).Row
If lRow > 1 Then
'~~> Set your Range
Set rng1 = .Range(aCell.Offset(1), .Cells(lRow, aCell.Column))
'~~> This will give you the address
Debug.Print rng1.Address
End If
End If
End With
End Sub
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)