Delete all rows in Excel document except Row 1

[TUSSFC]

I need some MS Access VBA code which will allow me to delete all rows from a specific workbook (which contains a single worksheet) EXCEPT Row 1 which contains the column header.

There is actually only 1 column of data (which gets populated with rows and rows of reference numbers) ... so this is kind of a "reset" function which removes everything and just leaves the column header ready for the next usage.

Any ideas?

 

[midmented]

Maybe this will help.

    Dim aRange As Excel.Range = WorkSheet.Range("C4:G4")
    aRange.ClearContents()

You would set the range of cells you want to clear and issue a ClearContents() on the range set.

 

[TUSSFC]

Thanks for the reply. I'm trying put this into code which opens the file first, etc.

Getting stuck ... have got this far:

Function ClearTemplates()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Dim wbSrc As Workbook
Set wbSrc = Workbooks.Open(Filename:="C:\Refs.xls")
Dim aRange As Excel.Range
Set aRange = Worksheet.Range("A2:A50000")
wbSrc.aRange.ClearContents
wbSrc.Close
objExcel.Application.Quit
Set objExcel = Nothing
Set wbSrc = Nothing
Set aRange = Nothing
End Function

Can't quite get it right :-/

 

[midmented]

Messed up!

I really blundered that code! I just started doing this forum help stuff.

ok, I tested this code and it worked perfectly for what you said you wanted to do.

Function ClearTemplates()
Dim oXL As Object
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oXL = CreateObject("Excel.Application")

On Error Resume Next
oXL.UserControl = True
On Error GoTo 0

On Error GoTo ErrHandle

Set oBook = oXL.Workbooks.Open("C:\Refs.xls")
Set oSheet = oBook.Worksheets(1)

oSheet.Range("A1:A50000").Select
oXL.Selection.ClearContents

oBook.Save

ErrExit:
       oBook.Close
       oXL.Application.Quit
       Set oXL = Nothing
       Set oBook = Nothing
       Set oSheet = Nothing
       Exit Function

ErrHandle:
      oXL.Visible = False
      MsgBox Err.Description
      GoTo ErrExit

End Function

 

[midmented]

Worked?

Just wondering if the last post of the corrected code worked for you?

 

[TUSSFC]

Just had a chance to test it & it workings great! Thanks