delete blank rows in new Workbook with VBA

Hi all, this is driving me completely crazy, i'm far from a vba expert, hope you can help.

So I have a workbook open with the following code behind a form control button.
It's supposed to create,name & save a new workbook and copy a range to it from a sheet (with a frozen pane) called MOM no named wsI in the code (stands for method of manufacture), then remove any blank rows.
I cant get it to remove the blank rows, i get a variation of error messages based around subscript out of range. I mention the frozen panes because It did briefly in a flaky way when i played around with freezing/unfreezing panes, but not robust enough to rely on, then it stopped.
It seems that when i create & save the new workbook (wbo) it is the active workbook as it is ontop of the original & is in focus but if i add a message box to return name of active workbook it shows (wbi). But whatever i do to try to make the right sheet active at the right time it doesn't work.

I don't mind going about it an entirely different way if anyone can suggest, all i want is a new workbook with the blank rows removed.

Many thanks in advance

VBA Code:

Please Login or Register  to view this content.

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

First things first:

Welcome to the forum.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Note how is :C represented in the code not in the tags.


As for merit:

Have you tried fully qualified references like wbo.wso.range … etc?
And doesn't

Please Login or Register  to view this content.

help?

Please attach a copy of your file as suggested in Post #2.

See Mumps1 suggestion.

As for better ways than delete row by row see such mod:

Please Login or Register  to view this content.

Replace this code:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Why not just copy the range excl blanks rows to start with...Perhaps a more suitable sample file upload of actual setup and then sample file of result required?

As for posts #8 and #9 - I think both the codes will delete rows with not empty rows, but just column A empty and some values in columns B:M.

Am I wrong?

As for #10 - healing properties of Excelforum.com server

May be: create new file, copy content of old one sheet by sheet, and module by module to a new one, save and reopen.

Hey QuantumSquirrel

Please take a moment to read our forum rules...here
Your post does not comply with Rule # 2
2. Programming code must be enclosed in code tags to improve readability. (A, Z)

Please Login or Register  to view this content.

So...Edit your post...Highlight the code and press the # button



Post 8 also works...

Please Login or Register  to view this content.

Clears filter

the code tags are missing in post #13

Originally Posted by QuantumSquirrel

sintek
i've already put tags round it?

Refer...Post 13

either way the filter code removes the first row

we have no idea what you are doing...We do not have your file...

Please Login or Register  to view this content.

Please Login or Register  to view this content.