code for data range selection to create a line graph in a macro

**dominicm** · 04-09-2015, 10:41 AM

Hello

I have some code in the start of my macro that looks for the last row in column P and then selects all that column all the way upto row 17 which works fine. The next bit of code was generated by me recording a macro of inserting a line graph of the data previously selected at the start of the macro and naming the graph "subtwist" and scaling it a bit to suit the size of my screen.

It all works fine but the problem is that it does not quite work how I need it when I run it on a different file. On the original file there was 3677 lines of data which you can see from the section of code where it is making the graph. If I run this macro on a new sheet that for example only has 1500 lines of data it produces a graph from 3677 lines. How can I make the graph select the data range that the first bit of code identifies?

thanks

here is the code I have in the macro.

Sub RawLogProcessing()
'
' RawLogProcessing Macro
'
' Keyboard Shortcut: Ctrl+d
'
' Select Data from bottom of column P up and stop at P17
'
Dim lr As Long
Dim rngAllData As Range

lr = Worksheets("Sheet1").Cells(Rows.Count, "P").End(xlUp).Row

Set rngAllData = Worksheets("Sheet1").Range("P17:P" & lr)
rngAllData.Select

'insert the subtwist graph

ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$P$17:$P$3677")
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.85625, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleHeight 0.9670140712, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.430976431, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0143626544, msoFalse, _
msoScaleFromTopLeft
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Subtwist"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Subtwist"
With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 8).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select

End Sub

**simarui** · 04-09-2015, 10:56 AM

change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**dominicm** · 04-09-2015, 11:20 AM

That worked just fine thanks. I want to add a graph underneath the first graph based on a different column of data. I tried to copy and past all the code and just change the references to column P to column J and give the graph a different title and thought that would work but it does not. When I run the macro it stop stops at the second line of "Dim lr As Long" and an error box pops up saying "duplicate declaration in current scope".

Another query is should I be splitting the code up into chunks, ie subroutine 1 for the first graph and subroutine 2 for the second one. If so how do I do this? I was somehow wanting manageable sections for each thing the macro is doing more for housekeeping to understand the macro when reviewing it say several months later.

Here is what I have in the code so far

Sub RawLogProcessing()
'
' RawLogProcessing Macro
'
' Keyboard Shortcut: Ctrl+d
'
' Select Data from bottom or column P up and stop at P17
'
Dim lr As Long
Dim rngAllData As Range

lr = Worksheets("Sheet1").Cells(Rows.Count, "P").End(xlUp).Row

Set rngAllData = Worksheets("Sheet1").Range("P17:P" & lr)
rngAllData.Select

'insert the subtwist graph

ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select

ActiveChart.SetSourceData Source:=Range("Sheet1!$P$17:$P$" & lr)
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.85625, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleHeight 0.9670140712, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.430976431, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0143626544, msoFalse, _
msoScaleFromTopLeft
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Subtwist"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Subtwist"
With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 8).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select

'Select Data from bottom of column J up and stop at J17
'
Dim lr As Long
Dim rngAllData As Range

lr = Worksheets("Sheet1").Cells(Rows.Count, "J").End(xlUp).Row

Set rngAllData = Worksheets("Sheet1").Range("J17:J" & lr)
rngAllData.Select

'insert the Vibration graph

ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select

ActiveChart.SetSourceData Source:=Range("Sheet1!$j$17:$j$" & lr)
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.85625, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleHeight 0.9670140712, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.430976431, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0143626544, msoFalse, _
msoScaleFromTopLeft
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Vibration"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Subtwist"
With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 8).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select

End Sub

**simarui** · 04-09-2015, 11:53 AM

1) you can remove the second line "dim lr as long" you've already declared lr as long within this sub and do not need to do so again. You'll also need to remove the second dim rngAllData line
2) it is pretty much entirely a matter of preference as to whether you break the code into two sections or not. do you want to be able to run these to subs separately? i.e. only create the second graph but not the first sometimes? if so then you could probably handle that with an if then type statement, but it may be easier / cleaner to break them into separate routines and associate each one with a different button or shortcut (or however you want to call them...)
3) to break the code into two separate subroutines, simply put End Sub at the end of the first and open up the second with a new title "sub MySecondSubroutine()" would do the trick. If you do so you will need to duplicate the dim lines i just told you you can remove.

As for the housekeeping:
I'd generally suggest this code is not all that complex and it is relatively straightforward to follow it, so you don't necessarily need any additional housekeeping, but your idea to break it into two sections would be fine, especially if they gradually get more complex over time...

some other ideas / rules of thumb -
whenever you have a block of code which is related in some way (as in after creating a loop or declaring with ___) i usually will indent the following lines of code - this helps keep things straight so people can jump straight through entire loops, if or with statements without necessarily reading every line but still understanding what they are doing.

generally you can delete any time you see ".select" as this basically does nothing as far as vba is concerned and is usually only input to help you in troubleshooting or stepping through each line to understand what it's doing. in some cases you may see back to back lines (especially from recording macros) where something ends with .select and the next line starts with selection. ... these can be merged into a single line by deleting ".select" and "selection"

it is generally good practice to avoid calling "active" anything - if the code is run while the user has selected another sheet vba will start performing operations on the new sheet... i'd suggest you are generally better off declaring your objects up front (i.e. dim ws as worksheet), setting those objects (i.e. set ws = sheets("MySheetName") and then calling the object later instead of the activesheet (i.e. ws.Shapes.AddChart2(332, xlLineMarkers))

some of the objects / variables / properties you see getting specified after recording a macro are optional and can be deleted as well. i'd suggest googling "excel chart objects" or visiting https://msdn.microsoft.com/en-us/lib.../ff194426.aspx or http://www.java2s.com/Code/VBA-Excel...sinVBACode.htm for more information on what the various methods / properties etc... are when working with charts.

i'd consider none of this mandatory, but these are the things i've picked up on over the last few months of trying to develop my vba coding abilities, and i think they help to give you a cleaner end result which is a little easier to understand (for those who are accustomed to reading vba anyway...)

**dominicm** · 04-10-2015, 12:03 AM

Simarui, Thanks very much for the time for your hints and tips and solving my problem. your help is very much appreciated and I will use your suggestions.

**simarui** · 04-17-2015, 09:35 AM

glad i could help and thanks for the rep! don't forget to mark the post solved

code for data range selection to create a line graph in a macro

LinkBack

Thread Tools

Rate This Thread

Display

code for data range selection to create a line graph in a macro

Re: code for data range selection to create a line graph in a macro

Re: code for data range selection to create a line graph in a macro

Re: code for data range selection to create a line graph in a macro

Re: code for data range selection to create a line graph in a macro

Re: code for data range selection to create a line graph in a macro

Thread Information

Users Browsing this Thread

Similar Threads

Create a code for offsetting range selection by 1 column

Code for Macro for Line-Column 2 axis (y) Graph

How to create a dynamic line graph with pre populated x and not y axis data

How to create 2d line Graph using connected cells of about 750 times using Macro???

How do you create a colored probability range around a graph line

Bookmarks

Bookmarks

Posting Permissions