Excel VBA Delete Empty Rows

In Access VBA:

This code, at least as I stare and study it, seems like it should work to delete all rows that contain no data:


' Define Excel file to open
Dim apExcel, wkBook, oSheet, RowCount As Long, iCount

' Create Excel object, set Workbook & Worksheets
Set apExcel = CreateObject("Excel.Application")
Set wkBook = apExcel.Workbooks.Open(sFileName)
Set oSheet = apExcel.ActiveWorkbook.Worksheets(1)

RowCount = oSheet.Rows.Count

For iCount = RowCount To 1 Step -1
    If oSheet.CountA(Selection.Rows(iCount)) = 0 Then
        oSheet.Selection.Rows(iCount).Delete
    End If
Next iCount

Open in new window


It keeps returning "Object Required" at the IF statement.  Is not oSheet the object?

Thanks,
BMITAsked:
Who is Participating?
 
gowflowCommented:
Try this
gowflow
' Define Excel file to open
Dim apExcel, wkBook, oSheet, RowCount As Long, iCount

' Create Excel object, set Workbook & Worksheets
Set apExcel = CreateObject("Excel.Application")
Set wkBook = apExcel.Workbooks.Open(sFileName)
Set oSheet = wkBook.Worksheets(1)


RowCount = oSheet.Rows.Count

For iCount = RowCount To 1 Step -1
    If Application.WorksheetFunction.CountA(oSheet.Rows(iCount)) = 0 Then
        oSheet.Rows(iCount).Delete
    End If
Next iCount

Open in new window

0
 
ragnarok89Commented:
This works
Sub DeleteBlankRows1()

'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.

Dim i As Long 

	'We turn off calculation and screenupdating to speed up the macro.

	With Application
		.Calculation = xlCalculationManual
		.ScreenUpdating = False

	'We work backwards because we are deleting rows.
	For i = Selection.Rows.Count To 1 Step -1
		If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
			Selection.Rows(i).EntireRow.Delete
		End If
	Next i
		.Calculation = xlCalculationAutomatic
		.ScreenUpdating = True
 	End With

End Sub

Open in new window

0
 
BMITAuthor Commented:
ragnarok,

I've tried that exact code, get same error.

gowflow,

I get a "Method or Data Member Not Found" error on the .WorksheetFunction of your solution.

Do I have to load a module or something to get .WorksheetFunction to work?  I'm using Access 2010, but this is saved the 2003 .mdb format.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gowflowCommented:
so your vba is in access ? yes pls load this refrence
Microsoft Excel 13 Object Library
I have Excel 2007 and it is Excel 11 so I suppose its 13 you look in the refrence list and you will see the Microsoft Excel correct refrence.
gowflow
0
 
BMITAuthor Commented:
I was able to load the 14 level objects.  Still no change.  I've been fighting this for an entire day.

I just can't understand why I can't use oSheet instead of the Application.WorksheetFunction.  It appears from my research that .CountA is a method that can be associated with a worksheet object.

Any other ideas?
0
 
gowflowCommented:
Well I ran it here and it gave me no error. Can you post your workbook that is doing the problem ?
gowflow
0
 
BMITAuthor Commented:
I just managed to get it to work with your code after I changed "Application." with "apExcel." on line 13 above.  Thanks for the help.
0
 
gowflowCommented:
Glad it finally helped you. Tks for the grade.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.