Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 2005 > Visual Basic 2005 Basics
| Search | Today's Posts | Mark Forums Read
Visual Basic 2005 Basics If you are new to Visual Basic programming with version 2005, this is the place to start your questions. For questions about the book: Beginning Visual Basic 2005 by Thearon Willis and Bryan Newsome, ISBN: 0-7645-7401-9 please, use this forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Basic 2005 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 20th, 2008, 04:15 AM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default [VB2005]Finding last cell of the column in excel

Is there any code where I can use VB 2005 to find the last cell of the specific column (usedrange)in Excel?

I am creating a chart using VB 2005 to plot in excel and the data will be random, so the last cell will not be fixed. But the first cell is not, but I'm still clueless on how to write that particular code.

There's one code in VBA which has the code:

Code:
ActiveSheet.Cells.SpecialCells(xlLastCell)

Does VB 2005 has the similar code like that?

I tried using VB object browser and help but to no avail.

Someone can give some guidance and I can figure out myself. Great thanks! Need to know urgently =)
 
Old June 20th, 2008, 04:30 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Cretae a project that uses Excel. Get to the point where there is a valid, instantiated, filld variable that references an active sheet.
Put a stop point on a line after that variable has gotten filled.
With the code suspended, not stopped, highlight that variable, right-click it, and use the pop-up menu to add it to the watch window.
Then start examining the variable’s (that is the object reference’s) properties, to try to discover a property similar to SpecialCells.
 
Old June 22nd, 2008, 11:33 PM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I dun understand..however...

I want to clarify..is End(Excel.XlDirection) the right function to find last used cell in that specific column?

Have been cracking my brain and looking thru threads in my weekend but still isnt getting anywhere.

here's the code I tried:
Code:
Dim rng As Excel.Range
Dim colNum As Integer

colNum = 2 
rng = WSheet.Cells(WSheet.Rows.Count, colNum)._
End(Excel.XlDirection.xlDown)

With xlsAxisCategory.Item(Excel.XlAxisType.xlCategory)
                .HasTitle = True
                .AxisTitle.Characters.Text = "Time"
                .HasMajorGridlines = True
                .HasMinorGridlines = True
                .MinimumScale = 0.479166666666667
                .MaximumScale = rng.Value '0.486111111111111
                .MinorUnit = 0.000347222222222222
                .MajorUnit = 0.000694444444444444
                .Crosses = XlAxisCrosses.xlAxisCrossesAutomatic
                .ReversePlotOrder = False
                .ScaleType = XlScaleType.xlScaleLinear
                .TickLabels.Orientation = XlTickLabelOrientation._
xlTickLabelOrientationUpward

            End With


The whole graph's x-axis went hay wire.
I place the rng.value at ".MaximumScale = rng.Value '0.486111111111111"
And '0.486111111111111 is actually the value I enter initially which the graph work perfectly.

'0.486111111111111 refer to the last cell i select in the 2nd column "B" (time column), which is actually 11:40:00 and the excel convert it to 0.486111111111111.

But I want the program to select last cell itself instead of me selecting it.


I'm not sure if I am using the correct function.


I also tried this code:
Code:
rangEndTime = WSheet.UsedRange._
End(Excel.XlDirection.xlDown).Row 

With xlsAxisCategory.Item(Excel.XlAxisType.xlCategory)

 .MaximumScale = rangEndTime.ToString '0.486111111111111

End with

The graph still haywire as well.





Similar Threads
Thread Thread Starter Forum Replies Last Post
[VB2005]Import textfiles to Excel using QueryTable bellaelysium Visual Studio 2005 0 July 28th, 2008 01:32 AM
Finding Column Range Nocturnl Excel VBA 3 December 29th, 2007 11:36 AM
Finding Column Name prabhakaran SQL Server 2000 3 December 4th, 2006 09:36 AM
Finding Adjacent Empty Cell keithd Excel VBA 7 July 19th, 2006 06:20 AM
finding and populating next available column rlrigsby Excel VBA 0 April 14th, 2006 04:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.