This code runs all the way to the end of my worksheet, evaluating EVERY cell in column G - I want it to only go say 679 iterations, but I can't get the syntax right - currently, it runs through every row - all 1,048,000+

that sounds peculiar. save and close the workbook.
open the workbook manually
press the Home End Home keys in order. What Cell does it take you to??

Do not run any macro other anything else, just open the workbook and do the home end home.

takes me to X1048576

then you have something physically in that cell

Nothing visible - Is there a way to clear all cells ? I tried simply doing a delete row - putting my cursor at the end of the data (Visible data), and doing a CTRL+SHIFT+END, then did a delete row, but I guess that didn't delete everything.

Steve

highlight all of the data you actually have and copy it to a new worksheet

Steve:

1048576 being the last cell in the worksheet, it means you do NOT have anything in any of the cells.

Steve, which specific part of the code you posted are you expecting to stop at 679 iterations?

You might try something like:

Dim x as Long
For x = 0 to 679
....Your Code
Next x

to limit it to 679.

This post has been edited by ipisors: Mar 26 2012, 01:55 PM

isaac

not sure what you mean. forgetting any vba whatsoever, home-end-home takes you to the last used cell for the current session of that worksheet. if you delete it, then save the worksheet and open it up again it wont take you to that position again.

if you open a brand new worksheet, and do home-end-home as the very first thing, your cursor shouldnt move from A1.

I don't think all users have the exact same experience with Home End Home. For me personally it doesn't work necessarily as you described. But to me the point is, if you are doing any (any one of a number of ways to: ) get to the last cell in the workbook, and if it takes you straight to row 1048576, that just means nothing was in between. 1048576 is the last physical row in v. 2007 and greater. I know we seem to discuss this 'last cell' concept a lot. I'm firmly against a top-down method of finding the last cell, I find it to be inaccurate or at best, just very misleading. Going from the bottom up is more reliable because then you know for SURE you're getting the BOTTOM MOST used cell. Just my opinion.

So if I were going to go the keystroke method, which I don't , but if I were, it would focus more on Ctrl+Home and Ctrl+End. But always starting at the bottom row and going up, not the top and going down.

remember, i am not talking about using it in VBA for the moment. just the simple usage of the microsoft method to reach the end of the active worksheet.

I see what you mean, I just don't use top-down methods as I've found them to be unreliable. Sorry that's what I meant (clarifying). I'm thinking that the OP obviously doesn't have data in 1048576, so something else is going on. Probably, hitting Ctrl+End twice, or "one time more" than takes you to the last row..

I avoid the method you use to determine the last row (top down approach) because it can be flawed (as experience has proven). I much prefer using



See http://www.devhut.net/2011/05/18/ms-excel-...rmine-last-row/ for a little more info.

I agree - sometimes 'invisible content' can confuse the heck out of a person.

So to take Daniel's post and then translate it into, if you prefer, a "non-VBA" solution, it might be like this:

Go to 1048576 and then do End, Home, or Ctrl+UpArrow if you know which column.

Daniel,

outside of VBA, just using keystrokes, have you ever seen end,home not work ?

If you want to loop through a range why not use a For Next with the last row in the range.

caught in a loop - just evaluates g2 and g3, over and over

It's probably not really the loop that's causing the problem, it's more likely to be the code within the loop.

In particular the use of ActiveCell and Select.

What in words is the code meant to do?

I've actually tried to work that out but all that Selecting hurts my head and I lose track of whether it's coming, going or moving in other mysterious ways.(IMG:style_emoticons/default/dazed.gif)

Bob G

I've seen it not working.

first of all, there is a date value typed in cell G9 in a worksheet named "ListsNotes" - the date is meant to be a threshold - it's currently 2/15/2012 - that's the cutoff date where if a report is prior to that date, the 'code' in col. F s/b "D" for delete - otherwise, it should be "A" active. In the future, I want to complicate things a bit, but I can't even get this to work :-(

We'll call that value the "TDATE," or threshholdDate

OK, in english:

Start in cell G2 - right now, there are 3 scenarios:

1. if value in G2 is empty or not a date,then ACTIVECELL.OFFSET(1,0) <aka go down one cell to evaluate cell G3>

if value in G2 IS a date, then evaluate it....
2. if it's LESS THAN THE TDATE, then go to the left one cell to the adjacent cell in col. F, and type a "D"

3. if it's GREATER THAN THE TDATE, then go to the left one cell to the adjacent cell in col. F, and type an "A"

go back to column G, and move down 1 cell

repeat 700 times

No, I can't say that I have.

so go from the bottom up ......... how would you know when you reached cell G1, and time to stop evaluating ?