Saved file size not reduced to original

[rustleg]

I have an Excell 2003 workbook with 6 worksheets and some VBA programmed macros in it. With a small amount of data the .xls file size is 150k. Then I tested it with a much larger sample of data, which goes into one of the worksheets, resulting size 3MB. Later on I reverted to reading in the original small amount of data but the file size stubbornly sticks to 400k instead of reverting to 150k.

The old data is definitely not there anymore - I used this code to clear it:

Please Login or Register  to view this content.

On further checking if I press ctrl + End it shows me a cell way down where the end of the old large data was. It seems I haven't cleared everything. Any suggestions as to how to clear it all?

[royUK]

You may need to reset the last cell, see this

[rustleg]

Thanks. That reference didn't say how to do it in VBA code, but you gave me the clue to google it and eventually find the answer.

After experimentation, I decided to just delete all rows below the actual data in my worksheet and this did the trick. The code I used is this

Please Login or Register  to view this content.

NXTDATAROW is the row below the last row in my worksheet

[martindwilson]

you should have used the add in,works on any file in a couple of clicks,great tool

[broro183]

Hi Rustleg,

To "futureproof" your macro code (in case you ever change to Excel 2007 or newer) I suggest changing your solution to remove the hardcoded value of 65k rows by using:

Please Login or Register  to view this content.

hth
Rob

[rustleg]

Good idea broro. I've amended it like you say and it works fine.

There is one strange thing I noticed. My version of the code is this

Please Login or Register  to view this content.

I noticed that ROWS is in uppercase. It also appears as uppercase in another place

Please Login or Register  to view this content.

The other uppercase stuff is my variables (I use uppercase to see which are my variables and which are Excel words). Strange that this is one of their words which is in uppercase but your example is all lowercase. Not a problem, more a curiosity. I'm using Excel 2003.

[broro183]

hi Rustleg,

My example is in lowercase because I typed it directly into the post so Excel didn't have a chance to modify it to appear as "Rows.Count". I'm surprised that it stayed as all uppercase for you because I think it should appear as it does above.

Do you have any variables called "ROWS"?
If so, I'd recommend changing them to prevent any confusion.

Another way of identifying your variables is to drop vowels (eg "Rws") this will completely prevent confusion. Also, it is good practice to type "Option Explicit" at the top of your modules to enforce variable declaration. This can be set as the default through the VBE menu - Tools - Options - Editor - & tick "require variable declaration".

Hth
Rob

[rustleg]

Do you have any variables called "ROWS"?

No, I have some with ROWS in such as SOURCEROWS. I would think ROWS would be disallowed as a reserved word, but I could be wrong.

I also have used "Option Explicit" as this makes the code safer from inadvertent errors. I've not declared anything called ROWS.

All my variables are in uppercase to distinguish them from Excels conventions. I tend to type the code in lowercase so that when the editor parses the line after entry the variables automatically change to uppercase (provided I already have a Dim statement with the variable set in uppercase).

Why I get ROWS in Excel statements is a mystery. However the code runs as it should do so it's causing no problem. As I said, it seems just a curiosity.

[shg]

You may have at some point. Just add and then delete this line of code:

Please Login or Register  to view this content.

[broro183]

Good point Shg,

I hadn't even considered a declaration & subsequent deletion.

Rustleg,
Rob Bovey has a useful Code Cleaner addin that cleans up the hidden junk that accumulates in VBE modules which I find very useful & it may fix this problem for you (+ others that you haven't found?). I recommend grabbing it from the below link, saving & installing the addin, opening a COPY of your file, checking the Code Cleaner options in VBE (untick "remove comments") & running the code cleaner on the COPY of your file to export & reimport the code.http://www.appspro.com/Utilities/CodeCleaner.htm

hth
Rob

[rustleg]

Thanks for the suggestions

I tried the Dim Rows idea - it changes the appearance of "rows" in the statements (even though these instances of "rows" are not variables)! However adding and deleting the dim didn't make any difference to this behaviour. I could manipulate the uppercase and lowercase in "rows" by different dim statements. It's clearly behaving as if it's a variable, it must be an issue with the VB Editor.

As to the code cleaner, I'm a bit reluctant to use miscellaneuos addins - even though I have no particular reason to believe it is not very good. As I understand it the function of this is to remove all code then reinsert it (I did read this somewhere else). I did that manually saving the file and quitting Excel before reinserting. It didn't make any difference to the "rows" issue.

All I can add is that I started this project in OpenOffice and switched to Excel because the OO macro language is poor (in my opinion). It didn't have any macros in it when I switched - just the sheets. Maybe it's something to do with that. However it's working fine so I'm not fussed in the end.

Thanks for your input shg and broro183.

[shg]

I too am a CodeCleaner advocate.

[broro183]

Thanks for the feedback - reluctancy aside, I do highly recommend Code Cleaner (as do some of the best in the business - Shg's advocation & a quick Google show this).

I like the concept of OO but have no experience so can't comment on the macro language (I think there are forums for it though?).

Earlier on you mentioned "reserved words" - I don't think that there are "reserved words"*, eg if you declare (dim) one of Excel's properties, objects or methods as something different than the "inbuilt declaration" I'm sure your declaration over-rides the inbuilt one for the scope of the declaration (sub, module, workbook etc).
You can test this in the Immediate Pane of the VBE (press [ctrl + G] to display it) while running your macro by typing "?typename(ROWS)" & [enter]. It should return "Range" but if you have it Dimmed as something else then it may return a variety of results. For more info highlight the "typename" word in the VBE & press [F1].

*Edit
Yes there are, I've just tried to redim "Date" but Excel wouldn't let me & the Help files led me to the phrase "restricted keyword" where Keyword is defined as

A word or symbol recognized as part of the Visual Basic programming language; for example, a statement, function name, or operator.

end edit

At a more philosophical level, if I were you I would be concerned if there was ever "an issue with the VB Editor" (but I don't think there is in this case) because how could you be sure your code is robust & trustworthy?

If you're happy with the discussion, can you please mark the post as "Solved"?

Rob

[martindwilson]

just wondering why add in not used? i presume it has all that code in it already?