1,105,534 Community Members

VB code to clear entire spreadsheet data (contents)

Member Avatar
tgifgemini
Junior Poster
113 posts since Jul 2007
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi all,
I need some input from you smart folks. I am populating an excel spreadsheet. Each time, I want to clear the spreadsheet content before repopulating it. I am using the codes below and it is clearing the first 3 rows in the spreadsheet and every other data stays in tact.
This is is my code:

xlWksht.Activate
    'xlWksht.Range("A1").Activate
    xlApp.Selection.CurrentRegion.Select
    xlApp.Selection.ClearContents

Any help will be appreciated.
Thanks,
tgifgemini.

Member Avatar
QVeen72
Veteran Poster
1,016 posts since Nov 2006
Reputation Points: 104 [?]
Q&As Helped to Solve: 164 [?]
Skill Endorsements: 7 [?]
 
0
 

Hi tgif,

u can clear the entire content.. but easy way round is to: First have a Blank File which is properly formatted, and When u want to create one, Copy the BlankFile to new Filename and modify it. Next when u want o create one more, Create with a new name or Kill previos file and copy to same name. Check This:

Say ur File Name is "C:\Myxl.xls"

If Dir("C:\myxlnew.xls",VbDirectory) <> "" Then
  Kill "C:\myxlnew.xls"
End If
FileCopy "C:\myxl.xls","C:\myxlnew.xls"

Regards
Veena

Member Avatar
tgifgemini
Junior Poster
113 posts since Jul 2007
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Good morning Veena,
thanks for your quick response as always. I set my code as you instructed but I am getting "Run-Time error 70 - permission denied" on this line:

Kill "C:\BiWeeklyPeriod.xls"

This is my entire code:

If Dir("C:\BiWeeklyPeriod.xls", vbDirectory) <> "" Then
       Kill "C:\BiWeeklyPeriod.xls"
    End If
    
    FileCopy "C:\BiWeeklyPeriodTemplate.xls", "C:\BiWeeklyPeriod.xls"

Also what is the syntax to clear Entire spreadsheet?
Thanks,
tgifgemini.

Member Avatar
QVeen72
Veteran Poster
1,016 posts since Nov 2006
Reputation Points: 104 [?]
Q&As Helped to Solve: 164 [?]
Skill Endorsements: 7 [?]
 
0
 

Hi,

I think u can open a worksheet..
try this code:

wrksheetobj.Cells.Clear

Regards
Veena

Member Avatar
tgifgemini
Junior Poster
113 posts since Jul 2007
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi Veena,
thanks. I accomplished the objective with this code:

xlWksht.UsedRange.ClearContents

tgifgemini.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article