Re: Whatever I do I get the wrong result

and for the special case, if you want the cells containing numbers (stored
as numbers) and they are constants not formulas

Dim rng as Range
set rng = Columns(2).Specialcells(xlconstants,xlNumbers)
for each ar in rng.areas
msgbox ar.address
Next

adjust to fit your scenario

--
Regards,
Tom Ogilvy


"Norman Jones" <normanjones@xxxxxxxxxxxxxxxxxxx> wrote in message
news:e$OaaOTBGHA.3936@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Arishy,
>
> The suggested use of
>
> > ActiveCell.End(xlDown).Select
>
> referred to your described scenario.
>
> If the active cell were blank, or the last populated cell in the current
> block of column cells, the above instruction would select the first
> subsequent populated cell, or the last cell in the column if the active
cell
> represented the last populated cell in the column.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Norman Jones" <normanjones@xxxxxxxxxxxxxxxxxxx> wrote in message
> news:uINNpHTBGHA.3488@xxxxxxxxxxxxxxxxxxxxxxx
> > Hi Arishy,
> >
> >> Range(activecell,activecell.End(xlDown).select
> >
> > Should read:
> >
> > Range(ActiveCell, ActiveCell.End(xlDown)).Select
> >
> > Note the second closing parenthesis.
> >
> > Your experience is consistent with your 'blank' cells not being empty.
> > Perhaps these cells contain a formula which returns an empty string. If
> > the intervening cells are not empty, the your code will not produce the
> > results you expect, precisely as John Walkenbach explains.
> >
> > Incidentally, if your intention is to select the last cell in the
current
> > block, perhaps try:
> >
> > ActiveCell.End(xlDown).Select
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Arishy" <arishy@xxxxxxxxx> wrote in message
> > news:1135059178.397559.80050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >> There is a weak area in VBA Excel that is driving me crazy.!
> >>
> >> The use of End(xlDown)
> >>
> >> Even John Walkenbach states it clearly in his wonderful book.
> >> "The use of End method may not produce the desired result..."
> >>
> >> There is only one place ...here... that I feel confident that will
> >> provide the answer..(Yes, I believe there must be a solution).
> >>
> >> To state this challenge as clear as possible :
> >>
> >> I have a column say B The column HAS blank cells
> >>
> >> Column A Column B:
> >>
> >> 1 Blank
> >> 2 1
> >> 3 1
> >> 4 1
> >> 5 Blank
> >> 6 Blank
> >> 7 2
> >> 8 2
> >> 9 2
> >> 10 Blank
> >> 11 3
> >> 12 3
> >> 13 3
> >>
> >>
> >> I need to get the ROW numbers for start and finish of group "1" etc
> >>
> >> If the activecell is on cells(2,2) the cell value "1"
> >>
> >> Range(activecell,activecell.End(xlDown).select
> >>
> >> I get the address of the last "3" cells(13,2)
> >>
> >> the "correct answer" should be cells(4,2).
> >>
> >> I am sure you will come up of a way around it, but how one using the
> >> END method come up with the code that will give reliable answer. and
> >> while we are at it WHY we get wrong answer in the first place.
> >>
> >
> >
>
>


.

Relevant Pages

  • Re: Error Checking Code, Excel 2000 & 2003
    ... Sub ErrorCheckTEST() ... Dim cell as Range, cell1 as Range ... msg= "ERROR Description is empty" ... MsgBox "ERROR Description is empty" ...
    (microsoft.public.excel.programming)
  • Re: Delete Table Rows (Blank) Macro Question
    ... I wish to just delete the empty ... Dim pCell As Word.Cell ... If pCell.RowIndex> pCurrentRow Then ... 'Current cell is not empty? ...
    (microsoft.public.word.vba.general)
  • Re: Error Checking Code, Excel 2000 & 2003
    ... 'checks each cell in row have data or no data next row if all blank end ... MsgBox "ERROR Description is empty" ... MsgBox "ERROR Type is empty" ...
    (microsoft.public.excel.programming)
  • Re: Copy contents of cells down
    ... Dim rng as Range ... > I recorded a macro that does a number of things. ... > rows there are and copy the contents of a given cell that number of rows. ... if its empty, then stop copying. ...
    (microsoft.public.excel.programming)
  • RE: Looping Through Variables
    ... i have always used Empty. ... In reference to clearing values in one instruction, its not the cell values ... Dim anyListEntry As Range ... Dim sourceWS As Worksheet ...
    (microsoft.public.excel.misc)