Working with cell ranges in Excel

[kodr]

I'm trying to create line graphs from some data being generated by another organization.

Say for instance I have the following data:

Processor Type1
             20-August  21-August  (continues for 30 days)
city1-proc1    20%         25%         etc...
city1-proc2    34%         20%         etc...
city1-proc3    10%         14%         etc...



Processor Type2
             20-August  21-August  (continues for 30 days)
city1-proc1    50%         75%         etc...
city1-proc2    84%         90%         etc...
city1-proc3    30%         16%         etc...

What I've done is to feed my graph generating code the 'allowed' range (say A2:S7) for the first and (A10:S16) for the second.

I end up with extra items in the legend that I assume are the unused rows in my source data (A5 thru A7 and A14 thru A16)

Can anyone suggest better way of passing the exact range I want to use? Keep in mind the sample data above is just for one city, there are currently over 30 cities and the number is growing constantly, so I'm trying to make this as dynamic as possible.

I will always know the starting location of the next set of data, but will not always know the number of processors (or rows of data) As this will change if someone slaps in a new card, or pulls one out. The limit is 5 per processor type though, so that makes it easier.

Is there anyway to give a starting position (A10) and get the 'usedrange' from there?

 

[SkipVought]

Hi,

If your data were set up in a table, with processor type as a column, you could simply use the AutoFilter to select only the data you want to plot, along with Table Option to Plot visible cells only.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[kodr]

Thanks, I've never looked into tables with Excel. This will give me something to experiment with.

 

[SkipVought]

Tables are the way to STORE data. Makes analysis, and reporting simple.

Your example is a REPORT format and not a STORAGE format.

I'd STORE tha data like this...
[tt]
ProcTyp CityProc ProcDate
[/tt]
Then I'd SUMMARIZE for the Chart Data, from the stored data table...
[tt]
ProcTyp CityProc ProcDate1 ProcDate2.....
[/tt]



Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[kodr]

Thanks again Skip. I'll definitely look into tables.

I don't have control on how the source workbook is created though, and this step is only a small part of a larger automation project.

I ended up using the following few lines of code to determine the last used row and column by specifying a starting cell for the range.

Dim startCell as String
startCell = "A40"
Set myRng = Worksheets("Sheet1").Range(startCell).SpecialCells(xlCellTypeLastCell)
lLastRow = myRng.Row
lLastCol = myRng.Column

I then feed lLastRow & lLastCol to my graphing sub, along with the known starting cell (startCell).

 

[SkipVought]

    Dim rng As Range
    With Worksheets("Sheet3")
    '1. Get the first cell in the first range
      Set rng = .UsedRange.Cells(1, 1)
      Do
    '2. Get the PLOT range
        With rng.CurrentRegion
          Set rng = Range(.Cells(2, .Column), .Cells(.Rows.Count, .Columns.Count))
          'plot the range
          rng.Select
    '3. Get the next plot area...
          Set rng = .End(xlDown).End(xlDown).End(xlDown)
        End With
      Loop While rng.Row <= .UsedRange.Row + .UsedRange.Rows.Count - 1
    End With

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[SkipVought]

Sorry, I had Sheet3 & rng.select for my testing purpose.

You ought to change the former to Sheet1 and remove the latter.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}