excel and cols/rows from vb

23 Sep 2005 12:09 PM

GrahamJWalsh

Hi,

I have a macro installed in excel that parses the contents of a
worksheet. The problem is that the current loop iterates over all cells
of the worksheet regardless of whether they are empty or not.

i.e.

last_col = ActiveCell.Column
last_line = ActiveCell.Row

this doesn' t take into consideration the fact that even though there
are 100 X 100 cells in the sheet, only 40 X 40 of them (say) are valid.
Anybody know what I can do to determine what method/function to use?

thanks much

G

23 Sep 2005 12:29 PM

NickHK

Graham,
ActiveSheet.UsedRange
Although not always 100% reliable depending on the situation.

NickHK

<GrahamJWa***@gmail.com> wrote in message
Show quoteHide quote

news:1127477347.875310.269150@o13g2000cwo.googlegroups.com...
> Hi,
>
> I have a macro installed in excel that parses the contents of a
> worksheet. The problem is that the current loop iterates over all cells
> of the worksheet regardless of whether they are empty or not.
>
> i.e.
>
> last_col = ActiveCell.Column
> last_line = ActiveCell.Row
>
> this doesn' t take into consideration the fact that even though there
> are 100 X 100 cells in the sheet, only 40 X 40 of them (say) are valid.
> Anybody know what I can do to determine what method/function to use?
>
>
> thanks much
>
> G
>

23 Sep 2005 12:54 PM

Matt Williamson

Graham-

I've found this page usefull in the past.

http://www.mvps.org/dmcritchie/excel/lastcell.htm

HTH

Matt

<GrahamJWa***@gmail.com> wrote in message
Show quoteHide quote

news:1127477347.875310.269150@o13g2000cwo.googlegroups.com...
> Hi,
>
> I have a macro installed in excel that parses the contents of a
> worksheet. The problem is that the current loop iterates over all cells
> of the worksheet regardless of whether they are empty or not.
>
> i.e.
>
> last_col = ActiveCell.Column
> last_line = ActiveCell.Row
>
> this doesn' t take into consideration the fact that even though there
> are 100 X 100 cells in the sheet, only 40 X 40 of them (say) are valid.
> Anybody know what I can do to determine what method/function to use?
>
>
> thanks much
>
> G
>

23 Sep 2005 2:05 PM

Jeff Johnson [MVP: VB]

<GrahamJWa***@gmail.com> wrote in message

news:1127477347.875310.269150@o13g2000cwo.googlegroups.com...

> I have a macro installed in excel

You used the word "macro." This suggests that you are using VBA from an
Office product. This group is for writing programs in full-blown VB. I
recommend asking your question in the group dedicated to VBA for your
particular product. Examples:

microsoft.public.excel.programming
microsoft.public.word.vba.general (or a more specific group in this
hierarchy)
microsoft.public.access.modulesdaovba
microsoft.public.outlook.program_vba