vba Excel Visual Basic 宏 - 为图表动态选择数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5048150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Visual Basic Macro- Selecting data dynamically for a chart?
提问by collisionTwo
So, this question is probably fairly stupid, but I'm not too familiar with Excel VBA. Here's my generated Macro code which imports data from a text file and graphs it.
所以，这个问题可能相当愚蠢，但我对 Excel VBA 不太熟悉。这是我生成的宏代码，它从文本文件中导入数据并将其绘制成图形。
Sub getData()
'
' getData Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\data.txt", Destination:=Range("$D"))
.Name = "data_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("D3:H4").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("Sheet1!$D:$H")
End Sub
Essentially, because the data inputted from data.txt can be any length, this program does not work; it just goes from D3:H3. I'd like the chart to use the data from D3 to HX, where X is the end of the data row. How would I do this?
Thanks for helping an idiot!
本质上，因为从data.txt输入的数据可以是任意长度，所以这个程序是行不通的；它只是从 D3:H3 开始。我希望图表使用从 D3 到 HX 的数据，其中 X 是数据行的末尾。我该怎么做？谢谢你帮助一个白痴！
采纳答案by Dr. belisarius
Probably the following will work:
可能以下方法会起作用：
Sub getData()
With ...
....
End With
LastRowColH = Range("H65536").End(xlUp).Row
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("Sheet1!$D:$H$" & CStr(LastRowColH))
End Sub
HTH!
哼！
回答by Patrick Honorez
For these thigs, I like to use a named range using =OFFSET().
See http://www.ozgrid.com/Excel/DynamicRanges.htmor google for "excel dynamic range offset"
对于这些 thigs，我喜欢使用 =OFFSET() 使用命名范围。
请参阅http://www.ozgrid.com/Excel/DynamicRanges.htm或谷歌“excel 动态范围偏移”
