Link to home
Start Free TrialLog in
Avatar of earngreen
earngreenFlag for United States of America

asked on

Range in Excel

I am trying to develop a piece of code that opens an excel document and if cells C1 to AM1 are blank then I want to delete the first row. The problem that I am having is how to identify the blanks. here is the code that i have been working with. Any suggestions?    

    Dim oexcel As Application
        oexcel = CreateObject("Excel.Application")
        Dim obook As Workbook
        obook = oexcel.Workbooks.Open("C:\somefile.xlsx")
        Dim osheet As Worksheet
        Dim myRng As Range
        myRng = Nothing
        osheet = oexcel.Worksheets("Sheet1")
        myRng = obook.Workbooks(1).Sheets(1).Range("C1:AM1").SpecialCells(XlCellType.xlCellTypeBlanks).Cells.Address

        On Error GoTo 0
        If myRng Is Nothing Then

            osheet.Rows.Delete(1)
            obook.Save()
            oexcel.Workbooks.Close()

        Else
        End If
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Try something like

If WorksheetFunction.CountA(obook.Range("C1:AM1")) = 0 Then obook.Range("C1").EntireRow.Delete
Avatar of earngreen

ASKER

Added appropriate references for WorksheetFunction but getting the error

Reference to a non-shared member requires an object reference

Not sure why this is happening
Can you show me the entire code and which line this error shows on?

Or even better, can you upload the workbook?
I am unable to load the worksheet and the entire code is listed. basically what is happening is I get a spreadsheet and sometimes it has an extra row for a file header and sometimes it does not. The file header occupies the A1 and B1. If there is no header then this is my column headers and they go from A1 to AM1. Once I determine there is a file header then I want to delete that row. For ex..

Empl_File_A| 11072012 <-- Need to strip this row when it appears
Empl_FName|Empl_LName| Empl_Address||||...........
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok try this

If WorksheetFunction.CountA(Activesheet.Range("C1:AM1")) = 0 Then activesheet.Range("C1").EntireRow.Delete