Last post Nov 19, 2008 12:22 AM by Namo_Siddhanam
Nov 19, 2008 12:22 AM|Namo_Siddhanam|LINK
I tried to google alot for deleting blank rows from excel sheet which was used by my asp.net appluication to display the data in grid...
after struggling alot for the same i developed a code in VBA to delete blank rows in excel sheet and i called it on save event of the same...
So below is the code to delete blank rows that is called when we try to save the excel sheet...
You can use the same code to be called either on Open event or on close event---for this what you need to do is
just create a new function named Auto_Open (Called when you open the excel sheet) Or
Auto_Close (called when you close the excel sheet) and copy paste the code :-)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim blank As Long 'Counter for blank cells
Dim lLoop As Long 'Counter for total number of cells in a row
Dim lRange As Long 'Counter for total number of active rows
Dim ColLength As Long 'Counter for active columns
blank = 0
ColLength = ActiveCell.SpecialCells(xlLastCell).Column
Application.ScreenUpdating = False
For lRange = ActiveCell.SpecialCells(xlLastCell).Row _
To lRange Step -1
If lRange <> 0 Then
'Loop to check for null values in all the cells of a row.
For lLoop = 1 To ColLength
If Trim(ActiveSheet.Rows(lRange).Cells(lLoop).Value) = vbNullString Then
blank = blank + 1
'Delete a row if all the cells in a row are null.
If (blank = ColLength) Then
Application.StatusBar = False
Application.ScreenUpdating = True
blank = 0
hope this post helps you
Macro to delete Blank rows in excel Sunny