Excel: How do I Skip Blank Cells

[sandy armstrong]

The Highlighted error Is THe b{intLastRow = ActiveSheet.Use dRange.Rows.Cou nt}

[Stewart Ross]

Variable intLastRow in the VBA code is defined as an integer value. Integer types are stored in two bytes and are limited to a max positive value of 32,767. Excel can have up to 65,536 rows (Excel 2003 and previous versions), or 1,048,576 rows (Excel 2007 and beyond).

To cure the overflow change the type of intLastRow to Long, which is stored in 4 bytes and has a maximum value of 2,147,483,647, which is well beyond the number of rows Excel can store.

Strictly, its name should also change to lngLastRow to reflect the type change, but in this case I'd just change the type alone for simplicity.

-Stewart

[sandy armstrong]

What do i change to replace it? Steward. By the ways thats for your help... :-)

[patjones]

You need to change Dim intLastRow As Integer to Dim intLastRow As Long.

[sandy armstrong]

Thanks every one so Much It is working now after I activate it copies the rows then goes into not responing mode. I know it because it is mostly like check every cell in the column. But again thanks Guys

[NeoPa]

Rabbit:
@NeoPa, I've seen that before, using the xlLastCell. But I heard it was erratic because Excel doesn't store the last cell well.

That's a surprise to me. I've been using it for ever and never seen any issues. Can you be more specific as to when/where to expect problems?

UsedRange.Rows. Count is actually not an indication of the last row. Typically, most worksheets start at the top so it it will have a value similar to that, but it really is just a count of the rows used. To see an illustration of this enter a value in a new worksheet in the cell N15. The value of UsedRange.Rows. Count shows as 1. Clearly that won't effect many worksheets, but you should be aware of it at least. There are various reasons why some worksheets don't start in row 1.

@Sandy
I'm curious. Did you have problems with the code I posted in post #12?

[sandy armstrong]

The code you gave me Neopa worked perfect for 3 times then it would say Run-time Error: overflow but i got it to work okay for now and i am very thankful this code has been a pain... But thanks to every help i got to run!
Sandy

[Rabbit]

Put data in rows 1 to 3, last cell is 3. Delete row 3, last cell is still 3. Put italics on row 5. Last cell is now row 5.

[NeoPa]

NeoPa:
That's a surprise to me. I've been using it for ever and never seen any issues. Can you be more specific as to when/where to expect problems?

That's to say I do know of an issue, but not one that would cause this code to fail to work as expected. I'll explain what I know and you can tell me if it's what you were thinking of.

When changes are made to a worksheet the value for SpecialCells(xl LastCell) reflects any increase in the spread of the data, but doesn't reflect any deletions until the workbook is saved. This can be seen by pressing Ctrl-End from the keyboard at any time. That takes you to the cell pointed to by SpecialCells(xl LastCell).

[NeoPa]

Rabbit:
Put data in rows 1 to 3, last cell is 3. Delete row 3, last cell is still 3. Put italics on row 5. Last cell is now row 5.

I guess I should have refreshed first :-(

What you say is exactly as it should work, apart from the failure to register the deleted rows until it's saved. Certainly that behaviour was factored into the code.

@Sandy
I'm surprised you say it overflowed. I can see why you may not want to worry about chasing that down as you already have some working code from Pat, but I admit I'm confused that anything there could overflow. I was careful only to use Long variables for Rows.

Nevermind eh. While you're reading though, and as you have got yourself a working answer from Pat's post #13, it may be a good time to set that post as the Best Answer for the thread.

[Stewart Ross]

@NeoPa: One of the less useful features of SpecialCells(xl LastCell) is that it counts pre-formatted but blank cells. So, if you develop a template with, say, conditional formatting applied to rows 1 to 100 columns A to F, then clear the contents, SpecialCells(xl LastCell) will always return F100 as the last cell used.

As long as specific formats are not applied to unfilled cells I have found SpecialCells(xl LastCell) completely reliable.

-Stewart

[patjones]

It's really just the code originally posted with a change in the While condition. It sounds like there still might be an overflow issue though.

[NeoPa]

@Stewart.
I don't believe a cell can ever be considered blank if it's formatted. That's the difference between the Clear and the ClearContents methods of Range. It's absolutely correct for SpecialCells(xl LastCell) to return the cell F100 in that situation. UsedRange works differently in that it reflects only those cells with values (visible or otherwise - by which I'm not referring to hidden cells but to those with empty strings or just spaces). The Address() method can be used to return the last row used if processed by Split(.Address, "$")(5).

In the case of finding the last used row of data I've always found the following concept (Used in my suggested code) to work perfectly (If it finds any valueless cells then this is minute as a problem and easily handled by the Ctrl-Up) :

1. Ctrl-End ==> .SpecialCells(x lLastCell).
2. Left x X columns ==> Find the cell in this row but in the column we're interested in (which we know to be populated for valid rows).
3. Ctrl-Up ==> .End(xlUp)