Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

Clear Cells from Specified Row in Excel

I would like to clear the spreadsheet from the specified Row at the click of a Command Button

Example:
I would like to clear from Row 13 in the Sheet1 till end, as i have my Spreadsheet heading above the Row 13 which i dont want to clear

What would be the VBScript code for the above
0
srikanthv2322
Asked:
srikanthv2322
  • 2
1 Solution
 
Saurabh Singh TeotiaCommented:

You can use the following code which will do what you are looking for...

Saurabh...
Sub cleardata()
    Dim lr As Long
    lr = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    If lr > 13 Then Rows("14:" & lr).ClearContents



End Sub

Open in new window

0
 
Christian de BellefeuilleProgrammerCommented:
You need it as VBScript or VBA for Excel?
0
 
Christian de BellefeuilleProgrammerCommented:
Well, if it's VBS you really need, here's how you do it...
just make sure that you change the c:\test.xlsm for the file that you need to clear the data.
Adjust the range properly, your sheet might have more rows & columns than that.

 
dim objExcel
dim objWorkbook
Dim objWorksheet

Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkbook = objExcel.Workbooks.Open("c:\test.xlsm")
Set objWorksheet= objWorkbook.Sheets(1)

objWorksheet.Range("A14:IV65535").ClearContents

objWorkbook.Close true
set objWorksheet = nothing
set objWorkbook = nothing
Set objExcel = nothing

Open in new window

0
 
srikanthv2322Author Commented:
Thank You
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now