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
rfedorovAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
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
 
rfedorovAuthor 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
rfedorovAuthor Commented:
thank you but i got the message
Run time error '9'
Subscript out of range
0
 
rfedorovAuthor 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
 
rfedorovAuthor 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
 
rfedorovAuthor 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
 
rfedorovAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.