Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel
Finding last cell in the column in excel Finding last cell in the column in excel
Finding last cell in the column in excel
Go Back  Xtreme Visual Basic Talk > > > Finding last cell in the column in excel


Reply
 
Thread Tools Display Modes
  #1  
Old 06-20-2008, 03:19 AM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default Finding last cell in 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 =)
Reply With Quote
  #2  
Old 06-20-2008, 05:31 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Bellaelysium,

That would work in VB.NET as well, but it returns the last cell within the entire used range on the worksheet, not for a particular column. (If all the columns have the same lengh, for example, within a table of data, then this would be fine.)

For other approaches, one can use WorksheetFunction.CountA() or Range.End() to find the last cell if the data is a solid block (no empty cells).

If there are gaps (empty cells) then using the Range.Find() method is usually your best bet. These approaches are outlined here:

http://www.ozgrid.com/VBA/ExcelRanges.htm

That thread is for VBA, but these same approaches work just fine in .NET as well.

Give it a shot...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 06-22-2008, 07:43 PM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

Thanks mike..

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.

Last edited by bellaelysium; 06-22-2008 at 10:26 PM.
Reply With Quote
  #4  
Old 06-22-2008, 07:50 PM
Rapiant's Avatar
Rapiant Rapiant is offline
Centurion
 
Join Date: Jun 2008
Location: Iowa
Posts: 111
Default

The Way I find the last column is this :

LastColumn = _
xlApp.Range("A1").SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Co lumn
Reply With Quote
  #5  
Old 06-22-2008, 07:59 PM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

Quote:
Originally Posted by Rapiant View Post
The Way I find the last column is this :

LastColumn = _
xlApp.Range("A1").SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Co lumn
thanks..

I actually want to find the last cell of that specific column. Not last column.


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.

Last edited by bellaelysium; 06-22-2008 at 10:26 PM.
Reply With Quote
  #6  
Old 06-23-2008, 08:21 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi bellaelysium,

I think your first try was closer:
Code:
Dim rng As Excel.Range
Dim colNum As Integer

colNum = 2 
rng = WSheet.Cells(WSheet.Rows.Count, colNum)._
End(Excel.XlDirection.xlDown)
However, you are begging the "search" at the location at the BOTTOM row, at 'WSheet.Cells(WSheet.Rows.Count, colNum)', in which case you want to "search" using 'XlDirection.xlUp', not 'XlDirection.xlDown'!

So I would try the following:
Code:
Dim rng As Excel.Range
Dim colNum As Integer

colNum = 2 
rng = WSheet.Cells(WSheet.Rows.Count, colNum)._
End(Excel.XlDirection.xlUp)
See how this works for you...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 06-24-2008, 07:28 PM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

hi mike,

thanks!
I'm getting there. ! The graph works!
I just had a talk with my supervisor and he actually want the axis format by itself depending on the time range that I collected from the sensors using PIC microcontroller.

Like if the time is from 11am to 5pm, the axis will format such a way that it will spread as an hour interval so that it will not be too clustered.

And if time is only four hours, like 3pm to 7pm, the VB must be able to auto format such that the interval will be create nicely and it will not be too wide a space in interval.

He want the chart to be read-only.

A question..: is this kind of auto format axes possible? If I uses something like "if statement".

Last edited by bellaelysium; 06-24-2008 at 07:47 PM.
Reply With Quote
  #8  
Old 06-25-2008, 03:38 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

In terms of "Auto-Formatting" the axis, you'll have to call your code setting the .MinimumScale and .MaximumScale (and/or other axis properties) according to the Max and Min of your data range.

The truth is, though, that Excel does a very good job with automatic scaling on its own. It's set to automatic by default when you first create the chart. Otherwise, you can set the axis to automatic scalling by calling .MaximumScaleIsAuto = True.

To make the chart read-only you need to protect the worksheet. (Use a password if you really want to make sure the user can't mess around with the worksheet.)

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #9  
Old 06-25-2008, 07:18 PM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

thks!

I'll try that.
Reply With Quote
  #10  
Old 06-26-2008, 05:27 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Sounds good, let us know how it goes...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 06-29-2008, 08:48 PM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

hello..

the code which was working fine last week, I dunno why it couldnt' work yesterday.
Code:
Dim rng As Excel.Range
Dim colNum As Integer
colNum = 2 
rng = WSheet.Cells(WSheet.Rows.Count, colNum)._
End(Excel.XlDirection.xlUp)

 With xlsAxisCategory.Item(Excel.XlAxisType.xlCategory)
                .HasTitle = True
                .AxisTitle.Characters.Text = "Time"
                .HasMajorGridlines = True
                .HasMinorGridlines = True
                .MinimumScale = 0
                .MaximumScale = rng.Value      
End with

I couldn't capture the maximum value in graph.

I have check and the error seems to be from this code:
Code:
WSheet.Rows.Count
I also even try this code
Code:
 rng = WSheet.Range("B65536").End(Excel.XlDirection.xlUp)
, but got the same result as using WSheet.Rows.Count .


Is there any problem or sth?

It works fine if i replace 'WSheet.Rows.Count' with 89(last row of B column in my excel spreadsheet) .

Oh..and for this code
Code:
'Time series for all six data series
            For i = 1 To 6
                objExcelC.SeriesCollection(i).XValues() =_
 "=AnimalRackTrial!R2C2 : R89C2"

            Next
is it possible to change the AnimalRackTrial to OpenDialogFile.txt and "R2C2 : R89C2" to rng.value (using WSheet.Rows.Count)?

I tried to but there's error running it. So Im not sure how to structure it.

Thanks!

Last edited by bellaelysium; 06-29-2008 at 09:01 PM.
Reply With Quote
  #12  
Old 07-01-2008, 07:42 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey bellaelysium,

I think what you want here is to start at the last row, and then use Range.End(xlDirection.xlUp) to find the last cell that contains data. Something like this:

Code:
Dim rng As Excel.Range
rng = WSheet.Range(WSheet.Rows.Count).End(Excel.XlDirection.xlUp)
For example, to get the row number or address for that last cell:

Code:
Dim rng As Excel.Range
rng = WSheet.Range(WSheet.Rows.Count).End(Excel.XlDirection.xlUp)

' Report the row number:
MessageBox.Show(rng.Row.ToString) 

' Report the cell address:
MessageBox.Show(rng.Address)
The above is untested, but I think should do the trick...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #13  
Old 07-01-2008, 07:12 PM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

hey mike..

there's some error running along this line:
Code:
rng = WSheet.Range(WSheet.Rows.Count).End(Excel.XlDirection.xlUp)
the old code, as I explain, it will work fine if i replace with numbers (e.g 100) instead of 'WSheet.Rows.Count'.

So I reckon there's some problem along there. I dun understand why it works fine last week but now it doesnt work.

WHat I want to do is to set the MaximumScale as maximum value (time series)

Hope you can help?

Million thanks!
Reply With Quote
  #14  
Old 07-02-2008, 02:22 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Hi bellaelysium,

Quote:
Originally Posted by bellaelysium
Code:
rng = WSheet.Cells(WSheet.Rows.Count, colNum)._
End(Excel.XlDirection.xlUp)
I also even try this code
Code:
 rng = WSheet.Range("B65536").End(Excel.XlDirection.xlUp)
, but got the same result as using WSheet.Rows.Count .
Both of these look fine to me. They should definitely find the last populated cell in the B column.

You say it is giving the wrong result, but that isn't much information for us to work with! So, if it is not finding the last cell which cell is it finding? What is wrong with the result?

My initial suspicion is that the problem is with the underlying logic rather than the code itself. Perhaps the issue is that the last cell doesn't contain the largest value: the largest value is elsewhere within the column? So your code would be executing correctly but not how you want it to. If that is the case then you should retrieve the largest value with the MAX function or, if the order of the data does not matter, you could sort the data in the column.

Hope that helps.
Colin
Reply With Quote
  #15  
Old 07-02-2008, 03:10 AM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

Maybe I never explain my problem clearly.

Hmm.. the code was able to work when I use
Code:
rng = WSheet.Cells(WSheet.Rows.Count, colNum)._
End(Excel.XlDirection.xlUp)
However, the graph look like this: (see attachment - errorgraph.jpg)

However, I replace WSheet.Rows.Count to 50 (last cell of B column),

Code:
rng = WSheet.Cells(50, colNum)._
End(Excel.XlDirection.xlUp)

the graph is what I want : (See attachment - correct.jpg)


The spreadsheet is taken screenshot as well to show which cell i want as maximum scale.

I cant put number as WSheet.Rows.Count since it will not be fixed value.


Part of code:
Code:
xlsAxisCategory = .Axes(, Excel.XlAxisGroup.xlPrimary)
            With xlsAxisCategory.Item(Excel.XlAxisType.xlCategory)
                .HasTitle = True
                .AxisTitle.Characters.Text = "Time"
                .HasMajorGridlines = True
                .HasMinorGridlines = True
                .MinimumScale = 0
                .MaximumScale = rng.Value          
                .MinorUnit = 0.0416666666666667 / 6  '10 minutes interval
                .MajorUnit = 0.0416666666666667      '1 hour interval
                .Crosses = XlAxisCrosses.xlAxisCrossesAutomatic
                .ReversePlotOrder = False
                .ScaleType = XlScaleType.xlScaleLinear
                .TickLabels.Orientation = XlTickLabelOrientation._
xlTickLabelOrientationUpward

            End With
I am going on my wit's end here as I dun understand why it doesnt work this week when it works last week.

Thanks for the help
Attached Images
File Type: jpg errorgraph.JPG (97.5 KB, 2 views)
File Type: jpg correct.JPG (167.4 KB, 2 views)
File Type: jpg spreadsheet.JPG (119.6 KB, 1 views)

Last edited by bellaelysium; 07-02-2008 at 03:15 AM.
Reply With Quote
  #16  
Old 07-02-2008, 03:22 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

And if you use WSheet.Rows.Count (which gives you the wrong result)
Code:
rng = WSheet.Cells(WSheet.Rows.Count, colNum)._
End(Excel.XlDirection.xlUp)
which cell is it finding? Is it not B49? Do you have any other data further down the B column?
Quote:
Originally Posted by Mike_R
Code:
' Report the row number:
MessageBox.Show(rng.Row.ToString) 

' Report the cell address:
MessageBox.Show(rng.Address)
Reply With Quote
  #17  
Old 07-02-2008, 03:30 AM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

There is no other data below B49. B49 is the last cell which is what I want but they wasnt able to take B49 as maximum scale.. Thats the problem which I cant figure out.

And my VB application is program such a way that any textfile it open, it will open in excel format and will select the last cell of B column to display as maximum scale.
Reply With Quote
  #18  
Old 07-02-2008, 03:36 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

If you include the Mike_R's code I re-posted in #16 within your procedure, please can you confirm what information is presented in the two message boxes?

Sorry to be insistent on this, but we need this information in order to be able to deduce what is 'going wrong'.

Thanks.
Reply With Quote
  #19  
Old 07-02-2008, 03:49 AM
bellaelysium bellaelysium is offline
Regular
 
Join Date: Jun 2008
Posts: 91
Default

hey..million thanks to you and mike.!

I tried mike code and the message said that it was at B890. I check all the way down and there's some value there which I think is entered accidentally and I didn't spot that.

Now it is solved. Thanks a bunch!
Reply With Quote
  #20  
Old 07-02-2008, 03:56 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

lol... you know I had a feeling that there was something lower down in the column!!

If you find that this could be a problem in the future and, if the chart data in column B is continuous (NO empty cells within the data itself) then you could retrieve the last cell using this alternative:
Code:
rng = WSheet.Range("B1").End(Excel.XlDirection.xlDown)
Colin
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel Finding last cell in the column in excel
Finding last cell in the column in excel
Finding last cell in the column in excel
 
Finding last cell in the column in excel
Finding last cell in the column in excel
 
-->