asked on

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 F

ASKER

I would like to include the file
Table.xls

Saqib Husain

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

Roman F

ASKER

thank you but i got the message
Run time error '9'
Subscript out of range

Roman F

ASKER

sorry, my bad

it is working
only one thing: the table border still remains, how to clear the border?

Saqib Husain

Are you using it on the same file or is it a different file?

Saqib Husain

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

Roman F

ASKER

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???

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roman F

ASKER

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

Saqib Husain

No, it does not remove any other formatting. You should try it again.

Roman F

ASKER

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

Saqib Husain

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