How can I create a macro that can detect data?

Question

Good afternoon!

I was hoping you could help me with Excel Macros: I am new to all this (I enjoy working with Excel, but I know way too little about it) and I have been assigned a project at work which requires me to make a macro on a workbook, and make it do several easy tasks, like create new columns with formulas, create pivot table, create charts, etc...

My problem is that now that I have all that, I realize that if the next chart (there will be one chart every 6 months or so) has even just 1 more row that the one I made the macro on, it will be excluded.

So what I am trying to find is a way to basically tell the macro: instead of just taking data from $A$1 to $BA$350, take all cells with data in them. So that if I add a row it automatically "sees" it and takes $A$1 to BA$351$. Some kind of "look at the next row. If there is data, take it. If not, stop here". (and same for the column).

I hope it makes sense, my apologies if it is confused, I have spent so long working on this that I am actually confused myself. Don't hesitate to ask me if you need more information to answer, and thank you in advance for your help!!

Anthony

(PS: it might not be important, but just in case, I work on Excel 2013, for PC).

possible duplicate of [Finding the last used cell in a spreadsheet](http://stackoverflow.com/questions/19971133/finding-the-last-used-cell-in-a-spreadsheet) — Ken White, Mar 05 '15 at 21:08
I might be wrong but it seems that this other question you are referring to does not solve my problem. I should have insisted on this in my question: I do not need the macro to find the very lats used cell in the spreadsheet, but rather to stop at the very first empty row. Like I said, I would like it, for every row, to "calculate": "is there data in the next row? if yes, continue, if no, stop". So that it includes the full chart but not the legend which is several rows below it. Apologies for the confusion. — Sorenn022, Mar 09 '15 at 19:15

score 1 · Answer 1 · answered Mar 05 '15 at 21:23

1

Here is an extremely simple example that you can adapt. Say we have data like:

enter image description here

But we don't know how far down the data will go. This macro will detect the limits and make a plot:

Sub Macro1()
    Dim N As Long
    N = Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("A1:A" & N)
    ActiveChart.ChartType = xlXYScatter
End Sub

enter image description here

answered Mar 05 '15 at 21:23

Gary's Student

95,722
10
59
99

Hi Gary's Student! Thanks a lot for your quick answer!! It works like a charm, but I still have one problem with it: there is, below the chart, a legend. Several empty rows separate it from the chart so I hoped it will not be taken into account by the macro but it is, which messes up the data. Is there a way to tell the macro to stop at the first empty row it encounters instead of keeping looking for data? thanks! – Sorenn022 Mar 09 '15 at 19:07
Ok I fixed the above thing by simply putting the legend above the chart and not below... I focus so much on the problems that I forget the simpliest solutions........ Now, Gary's Student, do you have a code similar to the one above but for row and column? I am trying to adapt this code for a pivot table's source range. The code currently looks like this: **SourceData:= _ "Report 1!R5C1:R355C58"** Now, I would like to apply that "Dim X as Long" to the R355C58 (both row and column), but even though I keep trying I cannot seem to find how to write it. Help? Thanks!! – Sorenn022 Mar 10 '15 at 19:50

score 0 · Answer 2 · answered Mar 05 '15 at 21:29

It sounds like you've used the macro recorder for the project you described above with charts. There is probably a line of code in there that reads something like this:

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$350")

or

ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$A$1:$B$350"

If you know that your data range is going to begin at a certain spot, let's say $A$1, then you can use the Range.End method to find the end of the data range and specify the data range for the chart.

For example:

Your data starts in $A$1 and goes down a number of lines, but you don't know how many. The first line of code from above becomes:

Activechart.SetSourceData Source:=Range(Range("A1"), Range("A1").End(xlDown))

Conversely, the second line from above would be changed to:

ActiveChart.FullSeriescollection(1).Values = Range(Range("A1"), Range("A1").End(xlDown))

Finally, if you want to just have the macro ask you what range to specify, you can use an Input Box to get the range. Add these lines to the top of the code:

Dim Rng As Range
Set Rng = Application.InputBox("Please select the range for the chart.", Type:=8)

Then, the two lines above become:

ActiveChart.SetSourceData Source:=Rng

or

ActiveChart.FullSeriesCollection(1).Values = Rng

Hope that helps! -Matt, via www.ExcelArchitect.com

Hi Matt! Thanks a lot for your answer. Unfortunately so far I have been unable to apply your solution to my macro (noob, I know). The last solution could work but I know my manager will not like it (he really wants it all to be automatic), and the previous ones I have tried to apply to the part of the macro where a formula is applied to a column up to a certain cell: " Range("F2").Select Selection.AutoFill Destination:=Range("F2:F350"), Type:=xlFillDefault" but I could not get it to work. Any tip? Thanks! — Sorenn022, Mar 09 '15 at 19:10

How can I create a macro that can detect data?

2 Answers2