Delete Empty Rows Macro
 

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.

Delete Empty Rows Macro
 

Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then
Rows(R).Delete
End If
Next R
End Sub


Gord Dibben MS Excel MVP

On Thu, 21 Aug 2008 19:57:02 -0700, yepiknowiam
wrote:

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.

Delete Empty Rows Macro
 

Hi,

The following macro should help. Take note of the comment re the column.

Ensure that you backup your workbook in case it does not do exactly what you
want.

Sub Delete_Rows()

Dim rngA As Range
Dim c As Range
Dim i As Long
Dim colCount As Long

'Assumes that column A will have data in the last cell
'within the range being tested. Otherwise change "A"
'to a column that does have data in last cell of total
'range being tested.

Set rngA = ActiveSheet.Range(Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp))

colCount = ActiveSheet.Columns.Count

'Work from bottom when deleting rows
With rngA
For i = .Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Range(Cells(i, 1), _
Cells(i, colCount))) = 0 Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub


--
Regards,

OssieMac


"yepiknowiam" wrote:

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.

Delete Empty Rows Macro
 

Thanks. I'll experiment with both of these and see how they work.

"OssieMac" wrote:

Hi,

The following macro should help. Take note of the comment re the column.

Ensure that you backup your workbook in case it does not do exactly what you
want.

Sub Delete_Rows()

Dim rngA As Range
Dim c As Range
Dim i As Long
Dim colCount As Long

'Assumes that column A will have data in the last cell
'within the range being tested. Otherwise change "A"
'to a column that does have data in last cell of total
'range being tested.

Set rngA = ActiveSheet.Range(Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp))

colCount = ActiveSheet.Columns.Count

'Work from bottom when deleting rows
With rngA
For i = .Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Range(Cells(i, 1), _
Cells(i, colCount))) = 0 Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub


--
Regards,

OssieMac


"yepiknowiam" wrote:

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.

Delete Empty Rows Macro
 

Everything works fine, but have another question. If I open up a different
workbook, the macro is not listed. Is there a way to save it or import it so
any workbook I open up, I will have access to it?

"Gord Dibben" wrote:

Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then
Rows(R).Delete
End If
Next R
End Sub


Gord Dibben MS Excel MVP

On Thu, 21 Aug 2008 19:57:02 -0700, yepiknowiam
wrote:

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.

Delete Empty Rows Macro
 

Gord's reply is much better. It's completely generic. We posted
simultaneously but had I seen his reply first I would not have posted mine.

--
Regards,

OssieMac


"yepiknowiam" wrote:

Thanks. I'll experiment with both of these and see how they work.

"OssieMac" wrote:

Hi,

The following macro should help. Take note of the comment re the column.

Ensure that you backup your workbook in case it does not do exactly what you
want.

Sub Delete_Rows()

Dim rngA As Range
Dim c As Range
Dim i As Long
Dim colCount As Long

'Assumes that column A will have data in the last cell
'within the range being tested. Otherwise change "A"
'to a column that does have data in last cell of total
'range being tested.

Set rngA = ActiveSheet.Range(Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp))

colCount = ActiveSheet.Columns.Count

'Work from bottom when deleting rows
With rngA
For i = .Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Range(Cells(i, 1), _
Cells(i, colCount))) = 0 Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
End Sub


--
Regards,

OssieMac


"yepiknowiam" wrote:

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.

Delete Empty Rows Macro
 

Lots of people who have this kind of useful macro create a workbook named
personal.xls (.xlsm). Then they store that workbook in their XLStart folder.

That way, excel will open this macro workbook whenever excel opens.

In fact, most people have this personal.xls hidden -- so it doesn't get in the
way of real work.

Then you can just open any workbook,
hit alt-f8 (tools|macro|macros in xl2003 menus)
select the macro and run it.



yepiknowiam wrote:

Everything works fine, but have another question. If I open up a different
workbook, the macro is not listed. Is there a way to save it or import it so
any workbook I open up, I will have access to it?

"Gord Dibben" wrote:

Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then
Rows(R).Delete
End If
Next R
End Sub


Gord Dibben MS Excel MVP

On Thu, 21 Aug 2008 19:57:02 -0700, yepiknowiam
wrote:

I have been looking for a macro that will delete any empty row. This
spreadsheet I work with generally has 3-4 empty rows after each section.

It doesn't have to be complex.

Thanks.



--

Dave Peterson