Thread: Selecting Cells between blanks for use in graphs.

I'm trying to write a VBA code that'll select cells in column B that contains text until it comes to a blank row, the amount of data changes each time, i've tried two different approches but i'm unable to get it to hold the cell, it only moves down one cell each time i run the macro, no errors though.

If anyone could take a looksie at my code so far and give me a point in the right direction it would be greatly appreciated

Code:

Sub Test()

With Range("B2", Range("B65536").End(xlUp))

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(Active) = True


End With
End Sub

I altso tried a slighty different approach but that didn't even move the selected cell down, no errors though..

Code:

Sub Tester()

Dim Var1 As Long, Var2 As Long
    
    Var2 = 0
    Do While Var2 > 0
        Var2 = ActiveCell.Value
        ActiveCell.Offset(1, 0).Select
        If Var1 = Var2 Then
        MsgBox "works" ' this is just to see if the code runs properly to this point.
        End If
    Loop

End Sub

Rather than the code can you post small example of the data that appears to be causing the problem.

Let us know what range you expect to be used.

This will select B1:B55 in your example file.

Code:

range("B1",range("B1").End(xlDown)).Select

I don't think you understood my question, i could easily write a 1 line of code that selects a predetermined range, but i want it to be able to search down the column and select all the cells until it hits a blank one, Since the amount of data will change over time. The only range i know that will be the same is B2. by the way that code will probably come in handy i didn't know you could express it like that and it'd walk down until the closest empty row(?), perhaps i could use it in some way or another.

Upon experimenting with that code i find that i cannot select more than 1 range, i need to select another column as well, but once i issue another range the first one will be lost.

Code:

Range("B2", Range("B2").End(xlDown)).Select
Range("E2", Range("E2").End(xlDown)).Select

So not to the blank cell but rather all cells in the column that have a value.

Code:

range("B:B").SpecialCells(xlCellTypeConstants).Select

Let's stop for a minute and work out exactly what the problem is.

You want to make multiple charts from the non contiguous data, yes?

If thats the case then we need more information.

Chart sheets or chartobjects?
If chartobjects, 1 per new worksheet or laid out on existing/new worksheet?
What type of chart?
What 2 columns will the chart be based on?

Chart Objects, Laid out on Existing Worksheet(001) More than 1 per worksheet, will differ depending on the amount of data.

columns B and E contain the data used for the graphs.

This is what i've got so far, Actually i think it just might be working now lol
Had some error earlier but reloaded the entire Worksheet and now it worked like a charm :D

Now i just need to have it jump down 4 blank rows and do the exact same thing for the next set of data on the spreadsheet, then for the next set of data the offset would be 2(Since the first breaker is 4 rows and the next ones are 2, dont ask me why, that's how i had to do it, but its the same for all my spreadsheets and it is a fact, and does not change.) order to create graphs for all the data in the spreadsheet(s).

Code:

Sub Test()

Dim Var1 As Range
Dim Var2 As Range
Dim mRange As Range

Set Var1 = Range("B2", Range("B2").End(xlDown))
Set Var2 = Range("E2", Range("E2").End(xlDown))

Set mRange = Union(Var1, Var2)
mRange.Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("001").Range(mRange.Address), _
        PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="001"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Norm Type vs Xbar"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Norm Type"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Xbar"
    End With


End Sub

Column B of your example files contains Norm_Type data such as A010 and B040. If the X values are Text then you do not need to specify a range.

try this on your sample file.

Code:

Sub Test()

    Dim rngData As Range
    Dim rngArea As Range
    Dim objChart As ChartObject
    Dim xx As Series
    
    Set rngData = Range("E2", Cells(Rows.Count, 5).End(xlUp)).SpecialCells(xlCellTypeConstants)
    
    For Each rngArea In rngData.Areas
        Set objChart = ActiveSheet.ChartObjects.Add(rngArea.Left + rngArea.Width, rngArea.Top, 400, 350)
        With objChart.Chart
            .PlotBy = xlColumns
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            With .SeriesCollection.NewSeries
                .Values = rngArea
                .Name = rngArea.Cells(1, 1).Offset(0, -3)
                .ChartType = xlXYScatter
            End With
            .HasTitle = True
            .ChartTitle.Characters.Text = "Norm Type vs Xbar"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Norm Type"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Xbar"
        End With
    Next

End Sub