?
Solved

Excel VBA Delete Empty Rows

Posted on 2011-10-05
8
Medium Priority
?
465 Views
Last Modified: 2012-05-12
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,
0
Comment
Question by:BMIT
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 36918421
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
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 36918664
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
 

Author Comment

by:BMIT
ID: 36918884
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 31

Expert Comment

by:gowflow
ID: 36918920
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
 

Author Comment

by:BMIT
ID: 36919397
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36919750
Well I ran it here and it gave me no error. Can you post your workbook that is doing the problem ?
gowflow
0
 

Author Closing Comment

by:BMIT
ID: 36919826
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36919883
Glad it finally helped you. Tks for the grade.
gowflow
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question