使用宏在Excel vba中绘制数据时选择数据的第一列到最后一列 [英] Selecting the first to last column of data when using a macro to graph data in Excel vba

问题描述

我正在编译一些工作中运行的测试数据，我希望从第一列到表单最后一列有一个宏选择数据。问题是每个输入文件的列数可能不同，所以我需要查找最后一列。

这是

 列（A：A）。选择
范围（选区，选区.End（xlToRight））。选择
 ActiveSheet.Shapes.AddChart.Select 
 ActiveChart.ChartType = xlXYScatterSmoothNoMarkers 
 ActiveChart.SetSourceData Source：= Range（_ 
'& fileType&'！$ A $ 1：$ H $& CStr（LastRowColH））
  

fileType是文件的名称，它可以很好地发现它，但是当Excel自动放置在$ A $ 1：$ H $范围内时会出现问题。我希望它从A列到最后一列有数据。该行的最后一部分，即CStr（LastRowColH））是我能找到的最接近的代码，它可以找到某个东西的最后部分。如果只有一个CStr（LastColRow1））代码来获取最后一列，而不是最后一行。

否则有一种方法可以设置$ A $ 1 ：$ H $部分等于活跃选择？

任何帮助将不胜感激！

p>

解决方案

源数据和图表是在不同的工作表上还是在不同的工作簿中？

  Dim c As Range，sht As Worksheet，cht As Chart 
 
 Set sht = ActiveSheet 
'假设以A1开始的常规数据块
 Set c = sht.Cells（1，Columns.Count）.End（xlToLeft）
 Set c = sht.Cells（Rows.Count，c.Column）.End（xlUp）
 
 Set cht = sht.Shapes.AddChart（）。Chart 
 cht.ChartType = xlXYScatterSmoothNoMarkers 
 cht.SetSourceData Source：= sht.Range（sht.Range（A1），c）
  

编辑：或者更简单地说：

  Set cht = sht.Shapes.AddChart（）。 Chart 
 cht.ChartType = xlXYScatterSmoothNoMarkers 
 cht.SetSourceData Source：= sht.Range（A1）。CurrentRegion 
  

I am compiling a bunch of data from some tests ran at work, and I want to have a macro select data from the first column all the way to the last column on the sheet. The problem is that the amount of columns may be different for every file that gets entered, so I need to look for the last column while it is being graphed.

This is what I am using so far.

Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range( _
            "'" & fileType & "'!$A$1:$H$" & CStr(LastRowColH))

fileType is the name of the file, and it works fine finding it, but the problem comes when Excel auto places in the $A$1:$H$ range. I would like it to go from Column A to the last column with data in it. The last part of that line, which is CStr(LastRowColH)), is the closest thing I could find to code that finds the last part of something. If only there was a a CStr(LastColRow1)) code to get the last column instead of the last row.

Otherwise is there a way I can just set the $A$1:$H$ part equal to the active selection?

Any help would be appreciated!

Thanks

解决方案

Are the source data and charts on different sheets or in different workbooks? It's not really clear from the code you posted.

Dim c As Range, sht As Worksheet, cht As Chart

Set sht = ActiveSheet
'assuming a regular block of data starting in A1
Set c = sht.Cells(1, Columns.Count).End(xlToLeft)
Set c = sht.Cells(Rows.Count, c.Column).End(xlUp)

Set cht = sht.Shapes.AddChart().Chart
cht.ChartType = xlXYScatterSmoothNoMarkers
cht.SetSourceData Source:=sht.Range(sht.Range("A1"), c) 

EDIT: or more simply just:

Set cht = sht.Shapes.AddChart().Chart
cht.ChartType = xlXYScatterSmoothNoMarkers
cht.SetSourceData Source:=sht.Range("A1").CurrentRegion

这篇关于使用宏在Excel vba中绘制数据时选择数据的第一列到最后一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！