[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Macro help -- clear rows instead of deleting them

Posted on 2012-09-20
7
Medium Priority
?
497 Views
Last Modified: 2012-09-24
Please can you help with the attached?

Basically the command button "Archive" moves the values occupied in column B:S onto the next worksheet "Archive"

The problem I have is that I don't want to delete the rows transferred, I would just like to clear the values in column B:N & R

The reason is that the formulas need to stay in situ and will reset once the values have been cleared.

If possible I would like to cap the limit on the "Archive" from line 9 to line 60

One other thing is that unless a date has been added in column N, the line will stay upon the current worksheet.

If you press the command button it should demostrate

Many thanks for your help!
help---11-09-2012.xls
0
Comment
Question by:Kiwi-123
  • 3
5 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38419134
It would make helping you easier if you didn't almost always use the same "VB Help" Title.
0
 

Author Comment

by:Kiwi-123
ID: 38419251
Okay MartinLiss, will bare that in mind.
0
 

Author Comment

by:Kiwi-123
ID: 38426382
I've requested that this question be deleted for the following reason:

Will re-post with new name
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 38427125
Try changing

            row.Rows(1).EntireRow.Delete

to

            row.EntireRow.Range("B1:N1").ClearContents
            row.EntireRow.Range("R1").ClearContents
0
 

Author Closing Comment

by:Kiwi-123
ID: 38428089
Many thanks for your help!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question