VBA .SpecialCells(xlCellTypeBlanks) – Only works on UsedRange? (Regardless of specified ra

VBA .SpecialCells(xlCellTypeBlanks) – Only works on UsedRange? (Regardless of specified range! )

Hi
. Here is a puzzler!
. I had inconsistent results trying to use .SpecialCells(xlCellTypeBlanks).Row to find the next free row in a main sheet in which to copy in data from a second sheet….
. I will try to demonstrate with a simplified example:
. Take this as my main sheet, ( Which is badly organized with empty rows! )

Using Excel 2007

-	A	B
1	Heading1	heading2
2
3	Something
4
5	Something2	Something3
6
7
8	Something5	Something7

MainSheet

…….

. Take this as my second sheet to be integrated into my main sheet :

Using Excel 2007

-	A	B
1
2	SomethingNewInA2
3
4	SomethingNewInA4	SomethingNewInB4
5	SomethingNewInA5	SomethingNewInb5
6
7
8
9	SomethingNewInA9	SomethingNewInB9
10	SomethingNewInA10	SomethingNewInB10

SheetToBeIntegratedIn

…
. So finally I would want This:

Using Excel 2007

-	A	B
1	Heading1	heading2
2	SomethingNewInA2
3	Something
4	SomethingNewInA4	SomethingNewInB4
5	Something2	Something3
6	SomethingNewInA5	SomethingNewInb5
7	SomethingNewInA9	SomethingNewInB9
8	Something5	Something7
9	SomethingNewInA10	SomethingNewInB10

MainSheet

. I wrote this simple code to achieve this:

Please Login or Register  to view this content.

.. However the code only gets this far before crashing ( Error 1004 )

Using Excel 2007

-	A	B
1	Heading1	heading2
2	SomethingNewInA2
3	Something
4	SomethingNewInA4	SomethingNewInB4
5	Something2	Something3
6	SomethingNewInA5	SomethingNewInb5
7	SomethingNewInA9	SomethingNewInB9
8	Something5	Something7

MainSheet

. This is a typical type one of the problems that I have had using .SpecialCells(xlCellTypeBlanks) . With this problem, somehow the .SpecialCells(xlCellTypeBlanks).Row does not like to go beyond the last used cell ??
. I have found a Bodge by experimenting that appears to cure the problem . That is to use the line 20 ( commented out in my code ). Strangely if I run the code again with both line 20 and line 30 in, then the program still works.
. very puzzling?
. Questions:
. 1 ) Can anyone explain this, as I do not like using codes or passing codes on that I do not understand.
. 2 ) is the answer that .SpecialCells(xlCellTypeBlanks) only works on the Current Region (Despite the fact that I have specified the entire column in line 90 !!! ( or line 91 ) ) . If so can anyone point me in the direction of any documentation stating this?

. Thanks Alan

You are correct, the SpecialCells(xlBlanks) method is bound by the UsedRange.

Originally Posted by JBeaucaire

You are correct, the SpecialCells(xlBlanks) method is bound by the UsedRange.

Hi JBeaucaire,
. Thanks for the quick reply.
. Do you happen to know of any documentation for this.? Or is it one of the small obscure things that only comes out after experience / experimenting? – ( The documentation I did “Google” gave the impression that my lines 90 or 91 would define the Range being looked at )
. Alan

One of those things you pickup accidentally when a macro you expect to write a million rows only writes 100, and you realize it's because the last used row currently is only row 100.... aha moment.


This actually becomes more logical when you start looking at the SpecialCells options and see one of them is "LastUsedCell", so it detects that UsedRange by design right out of the gate.

. Ok thanks JBeaucaire, That is making everything crystal clear now…
. For example. In the following code I Assume I start with a fresh unused sheet in a workbook. I then try to run the code:

Please Login or Register  to view this content.

…..
. It will not work! - Lines 40 70 90 100 and 110 would all error as I never had anything in column C. ( I have no Used Range )
. If I put something in any cell in column 3 ( for example cell C15 ) and then remove it and then re-run the code it will all work. ( I now have a Used Range ( Even if it is empty, - the subtle point, as ever, about UsedRange being that it refers to a range that includes any cells ever used ( Or should### )) )
. Line 40 gives the row number of the first empty cell, staring from the top,
. Line 70 gives me the row of last cell used, 15 in this case, (regardless of whether it is now empty or not ).
. Line 90 fills to row 3
. Lines 100 and 110 give the same result, that is to say filling cells up to row 15, ( the Last cell that I used )

. So the bottom line is that

Originally Posted by JBeaucaire

….. the SpecialCells(xlBlanks) method is bound by the UsedRange.

. A jem of info I still have not found written clearly anywhere yet, but as you say seeing now the option xlCellTypeLastCell , gives a hint in that direction. That is maybe saying more exactly that the .SpecialCells Method is bound by the UsedRange***, so that will include SpecialCells(xlBlanks). A Subtle point, but one which throws a beginner off course as the documentation, as often is not clear. . . Thanks for sharing your knowledge
.

Thanks a lot
Alan "holey=cow" Elston

P.s.
*** . A last subtle point. Say I repeated the above experiment staring again initially with a fresh unused Sheet and using the same code. As before it will error at the Lines indicated. However, if then rather than putting something in say cell C15, I put something in cell B15, then the code will still not work. It will however work giving the same results as before if I put anything in a cell such as D15 or H15 etc.
. The point here to emphasize is that .SpecialCells Method is bound by the UsedRange of the sheet referred to and not necessarily a used Range within the specified range.
. And further more my experiments indicate that actually the .SpecialCells Method is bound by the Upper limit of the sheet Used Range, not necessarily the actual used range. My code will still work fully in a fresh sheet giving the same results if I, use and them empty , say, cell C15 and C14.
. However there are some very strange “quirks” I found, such as using the UsedRange.Row or UsedRange.Rows.Count method after emptying the cells sometimes reset the Used Range in the sheet to nothing which then prevented my code from working… ( Clearly this contradicts the idea that Used Range always applies to the last Range used ### !?!) . So clearly exactly what is going on is not understood completely by anyone, and that maybe explains why the documentations is so bad!!! ) .........
... But i will mark the Thread as solved...It is probably solved as far as humanly possible!!

Originally Posted by Doc.AElstein

......
. However there are some very strange “quirks” I found, such as using the UsedRange.Row or UsedRange.Rows.Count method after emptying the cells sometimes reset the Used Range in the sheet to nothing which then prevented my code from working…....

. Hi Alan,
. 1) It would appear that any reference to UsedRange forces Excel to update its memory of the Used range. Again documentation appears imprecise on this one.
.
. Presumably in your experimenting with the .SpecialCells where you placed something in a cell and deleted it, VBA remembered this entry, taking it then as part of the used Range. Your results tended to prove this. Unfortunately you cannot prove it through use of a UsedRange property such as .Rows.Count, or .Row etc. , as the use of the UsedRange Method updates to the current Used Range. We can however find the last Row ( or Column ) used from .Cells.SpecialCells(xlCellTypeLastCell).Row - As this does not appear to update the memory of the last Used Range

. Here a code to step through ( applied to a fresh sheet ) to demonstrate the above points:

Please Login or Register  to view this content.

…………………………………….
. Final conclusions:

If rng is some specified range in a worksheet, then for .Rng.SpecialCells

. 1 ). - the lower limit is set by that of rng lower limit ( provided there is a used range anywhere in the sheet )

. 2) – the upper limit is set by either
. 2a) rng upper limit
or
. 2b) the Last UsedRange upper limit of the sheet. ( except in cases noted in .7) below
. Which of the above a) or b) applies is that which is the smallest. ( except in cases noted in .7) below )
. Note: The UsedRange of the sheet must intersect the rng, or else the upper limit is zero and .SpecialCells will not work.
.
. 3a) The VBA memory of the last Used Range is not reset by .UsedRange.Clear, which sets the used range to nothing

. 3b) The VBA memory of the last Used Range is usually ( see .7) below ) reset by use of the UsedRange Properties
.Rows.Count / Row / Column / Columns.Count

. 3c) The VBA memory of the last Used Range upper limit can be found by
.SpecialCells(xlCellTypeLastCell).Row
and
.SpecialCells(xlCellTypeLastCell).Column
. ( These two statement do not reset the VBA memory of the last Used Range )

. 3d) After using .UsedRange.Clear , if an entry or entries made anywhere in the sheet , then the VBA memory will be reset to the "Box" including the cells with those entries in them

. 4) The actual Used Range is the "box" including cells with any entries in them.

. 5 ) The last Used Range, ( held in memory) remains this range from . 4) until
. 5a) an entry or entries are made in cells outside this "box" which increases the Used Range to a new bigger "box" to include the new cells
or
. 5b) The Used Range is cleared to nothing AND a new entry or entries are made

( . 6) note / reminder:- "Entries" can also be formatting such as text type of a cell ( even if no actual text is written in the cell ), or color etc. Etc. These formatting can only be reset to nothing, or rather to “as it was before any entry was made” by .Clear or by deleting the row or column in which that cell with a new entry is in )

. 7) As always a last bit of VBA “madness” that defies all sane reasoning: . If the sheet has a row with a format change of the sort such as row height, this will not be cleared by .Clear.
.UsedRange will recognise this row. So will .SpecialCells(xlCellTypeLastCell).Row and this will return a value equal to the row with this changed height. ( This memory will in this case not be reset by the UsedRange Properties .Rows.Count / Row / Column / Columns.Count – this is expected as .Clear for example does not remove it and .UsedRange still recognises it )
. However in this case, strangely, it will not be used to set the upper bound of .SpecialCells ..... But wait for it.... If there is or was any other entry or entries in the sheet anywhere resulting in a sheet Used Range intersecting the defined rng for .SpecialCells that were not cleared then it will be used as the upper bound of .SpecialCells !!! AARRRRGGGHHHHH!

. Alan

. P.s.: Another couple of last small “anomalies”: UsedRange.Row or UsedRange.Column Or UsedRange.Rows.Count etc. Return 1 rather than zero for no Used Range., ( either from a “virgin” sheet or a sheet emptied through .Clear ) Similarly things like .SpecialCells(xlCellTypeLastCell).Row return 1 rather than zero after the VBA Used Range has been reset to nothing