data and macros

arab

New Member
Joined
Aug 25, 2002
Messages
11
hello all

i have a macro that imports data from a text file and then creates a chart from this data. The problem is that the data in the text file varies and i need the macro to read from a specified cell to the end of the column/row. At the moment it just selects the same cells regardless of the amount of data imported and therefore misses some data for the chart.

here's the code generated by excel....

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:B8"), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
Sheets("Chart1").Select

is there a keyword with the .Range( ) call to specify from a point to the end of the data e.g .Range("A2:END")??

v.simple i know but can anybody help

cheers
This message was edited by arab on 2002-08-26 11:56
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
To find the last row used in a column:

LastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row

then change this:

Range("A2:B8")

to:

Range("A2:B" & LastRow)


_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-08-26 12:24
 
Upvote 0
jim

thanks for the reply. tried it out but got a run-time error saying that the object doesn't support the property or method

any ideas??

cheers for any help
 
Upvote 0
OOOPS

LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row

_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-08-26 12:54
 
Upvote 0
hey jim

sorry to bother you again but it still ain't working. here's how i've included it into my code.......

Charts.Add
LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:B" & LastRow), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
Sheets("Chart1").Select

cheers
 
Upvote 0
Where is it failing? What error are you getting? If you have OPTION EXPLICIT turned on... have you defined LastRow (as long)?
 
Upvote 0
here's how i wrote it. probably got loads wrong. havn't got a clue about syntax and structure for vb.

Sub graph()
'
' graph Macro
'
' Keyboard Shortcut: Ctrl+b
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:Documents and SettingsgeorgeMy Documentsprojectexcelinfo.txt", _
Destination:=Range("A1"))
.Name = "info"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.Refresh BackgroundQuery:=False
End With
Charts.Add
LastRow As Long
LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B" & LastRow), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = " Number"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

cheers for any help
This message was edited by arab on 2002-08-26 14:20
This message was edited by arab on 2002-08-26 14:21
 
Upvote 0
arab,

I haven't worked much are charts, so I may be little help here, but if you could tell me where is it failing, what error are you getting I might be able to help. One thing you DO need to change is:

Change:
LastRow as Long
To:
Dim LastRow as Long

... and move it just below the following:
' Keyboard Shortcut: Ctrl+b
 
Upvote 0
hey jim,

managed to sort it out. silly old me was putting the

LastRow = ActiveSheet.Cells(65536, 2).End(xlUp).Row

outside the Activesheet scope so it didn't have a clue what i was talking about. I was also declaring the Dim LastRow within the chart scope instead of outside like you suggested
give me java or c++ anyday..........

thanks for your help

back to the grindstone...

arab
 
Upvote 0

Forum statistics

Threads
1,223,165
Messages
6,170,468
Members
452,329
Latest member
Irefsports

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top