How to delete all rows except Row 1 and a variable row?

[tnuis]

Hello,

I want to start fresh on a worksheet whenever unloading the userform.

This means deleting all rows on the active worksheet except:
- Row 1 and
- the row that contains the text "Totalt:" in column B. This row is a variable, depending on how many items (rows inserted in between) have been added by the user.

What happens is that every time an option is chosen in a configurator in the userform a row containing data is inserted between the 2 rows mentioned above.

Thus when unloading the userform I want it to start at scratch again, just containing data in the rows above. This is my DEFAULT state if you like.


This is a picture of my desired DEFAULT state of the worksheet:

Screenshot 2014-11-30 16.41.43.png


Thanks in advance

[mehmetcik]

This is quite simple

Just paste this code into the userforms vba module.

Please Login or Register  to view this content.

[stnkynts]

Please Login or Register  to view this content.

[tnuis]

Hi guys,

Thanks for your solutions. both didnīt work 100% as a wanted but this might because maybe I wasnīt completely clear. However I add a line of code to stnkynts solution and now it workes like a charm.

mehmetcik: Your code did not clear the content of row 2 itself. But off course this would also be easy to fix by adding a line of code to simply ClearContents of row 2.
After some more extensive testing I found that after a couple of times i suddenly get the Run-time error '91': Object variable of With block variable not set. In debugging mode the 3 lines under Columns("B:B").select are marked yellow.


In other words, both codes does the work!

Thank you so much guys

This is the code I ended up using that seems to be the most stable solution so far:

Please Login or Register  to view this content.