如何在非连续列中选择一系列单元格，直到最后一个空行

Question

我正在使用宏来从数据库创建图表，我需要在3个非连续列(a，d和k(中选择数据。

Private Sub TRAFFIC_BT_Click()
'
' web_traffic Macro
' Create a chart to show the evolution of web_traffic stats
'
'
    Dim szTodayDate As String
    Dim LastRow As Long
    Dim WS As Worksheet

    Set WS = Sheets("DB")
    LastRow = WS.Range("A" & Rows.Count).End(xlUp).Row 'Finds the last row with text

    szTodayDate = Format(Date, "mmm-dd-yyyy")
    Application.ScreenUpdating = False
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.ChartTitle.Text = "Web Traffic Report " + szTodayDate
    ActiveChart.SetSourceData Source:=Range("DB!A1:A72, DB!$D1:$D72, DB!$K1:$K72")
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).AxisGroup = 2
    ActiveChart.SetElement (msoElementLegendBottom)
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Web Traffic report " + szTodayDate

End Sub

使用此代码，我将在图表中绘制从A1到A72，D1到D72 ...但是我想更改Lastrow的" 72"

Answer 1

连接拉斯特罗变量到范围内而不是使用72。

Private Sub TRAFFIC_BT_Click()
'
' web_traffic Macro
' Create a chart to show the evolution of web_traffic stats
'
'
    Dim szTodayDate As String
    Dim LastRow As Long
    Dim WS As Worksheet

    Set WS = Sheets("DB")
    LastRow = WS.Range("A" & Rows.Count).End(xlUp).Row 'Finds the last row with text

    szTodayDate = Format(Date, "mmm-dd-yyyy")
    Application.ScreenUpdating = False
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.ChartTitle.Text = "Web Traffic Report " + szTodayDate
    ActiveChart.SetSourceData Source:=Range("DB!A1:A" & lastrow & ", DB!$D1:$D" &  lastrow & ", DB!$K1:$K" & lastrow & ")
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).AxisGroup = 2
    ActiveChart.SetElement (msoElementLegendBottom)
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Web Traffic report " + szTodayDate

End Sub