How to delete the data in excel sheets

I have an excel book with thre sheets (tabs). The first one is summary, two others are contain data. Let say sheet A,sheet B,sheet C,
Sheet B and Sheet C looks like
Name Count
Steve 100
Piter  50
Ann 25
I would like to run macro which will delete Sheet B and sheet C data but leave the header and format of the columns
The header is Name and Count, the format is string and number accordingly
Roman FAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roman FAuthor Commented:
I would like to include the file
Table.xls
0
Saqib Husain, SyedEngineerCommented:
Try this sub

Sub deldata()
Application.Intersect(Sheets("B").UsedRange.Offset(1, 0), Sheets("B").Columns("A:B")).ClearContents
Application.Intersect(Sheets("C").UsedRange.Offset(1, 0), Sheets("C").Columns("A:B")).ClearContents
End Sub

0
Roman FAuthor Commented:
thank you but i got the message
Run time error '9'
Subscript out of range
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Roman FAuthor Commented:
sorry, my bad

it is working
only one thing: the table border still remains, how to clear the border?
0
Saqib Husain, SyedEngineerCommented:
Are you using it on the same file or is it a different file?
0
Saqib Husain, SyedEngineerCommented:
You had mentioned

"delete Sheet B and sheet C data but leave the header and format of the columns"

But if you like to clear that also then change clearcontents to clear

Saqib
0
Roman FAuthor Commented:
You are right, if   i will clear insead of ClearContent it will delete *
But i want to leave the format of the data, like number and text
so i need to remove the border of the table...

Sheets("B").Selection.Borders.LineStyle = xlNone---somethhing like that???
0
Saqib Husain, SyedEngineerCommented:
OK try this

Sub deldata()
Application.Intersect(Sheets("B").UsedRange.Offset(1, 0), Sheets("B").Columns("A:B")).ClearContents
Application.Intersect(Sheets("B").UsedRange.Offset(1, 0), Sheets("B").Columns("A:B")).Borders.LineStyle = xlNone
Application.Intersect(Sheets("C").UsedRange.Offset(1, 0), Sheets("C").Columns("A:B")).ClearContents
Application.Intersect(Sheets("C").UsedRange.Offset(1, 0), Sheets("C").Columns("A:B")).Borders.LineStyle = xlNone
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roman FAuthor Commented:
thank you, the second and fourth line is the same as Clear, it removes the formating of the column
which is not good,
ok, i am really appreciate your help. I will ask Experts about another problem how to make the Coumn B Number
0
Saqib Husain, SyedEngineerCommented:
No, it does not remove any other formatting. You should try it again.
0
Roman FAuthor Commented:
well, the format of the column B was a number, with 0 decimal places and use a comma as a 1000 separator. Now it is gone and when i enter the number for example 2500 insead of 2,500 it stays as 2500
0
Saqib Husain, SyedEngineerCommented:
When I run the macro the number formats are not disturbed.

I suggest that you exit excel, download the file which you have uploaded on this page and then try it again.

Saqib
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.