Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

Hi Kaj,
This has been answered very, very many times. If you do a Google search
within *.excel.* as a group, over just the last year, using "rows" as a
"Search for" criterion, you will come up with a variety of good
solutions.

HTH
Dave Braden

In article ,
"Kaj Pedersen" wrote:

Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen



--
(ROT13)

assume an empty row would have a blank cell in column A, other wise it would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

Or testing the whole row

Sub DeleteEmptyRows()
'JW
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
Next R
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
assume an empty row would have a blank cell in column A, other wise it would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
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
Next R
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

--
(ROT13)

Hi David

I have save the sub to test it this weekend.
It looks good

Thanks for posting it


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"David J. Braden" wrote in message ...
Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
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
Next R
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a specified
range?
I hope someone can help.

Regards
Kaj Pedersen

--
(ROT13)

Ron,
Thanks for the promised test. As you know, I do this off of Mac/Unix, so
I can only hazard a guess how it will work out under Windows..

The other issue that raises its ugly head is "what is a blank cell"?

Idea (I learned from Myrna Larson) is to build up what you can, then do
the action to get the worksheet side of things to do the looping, as
that's generally much more efficient than looping with VBA through
worksheet stuff like this.

As you know, .Item(l).SpecialCells(xlCellTypeBlanks).EntireRow is the
entire row *within UsedRange* as opposed to having Excel check all 256
cells in a row.

Please post any improvements you can think of.

Also, I don't need one line I had, given the code structure, and had an
unneccesary Dim. So replace what I posted with:

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow

For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub

Could be there's a tradeoff as far as the sha[e of the worksheet; mine
almost always have far more rown than columns, so what I suggest will
likely be quite a lot faster, on average.

Regards,
Dave B

In article ,
"Ron de Bruin" wrote:

Hi David
I have save the sub to test it this weekend.
It looks good
Thanks for posting it
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"David J. Braden" wrote in message
...
Ron,
This appears substantially faster than John's and Chip's approaches,
from test data I've devised. Can't say it's always faster.

Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngCol As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow
If rngDel Is Nothing Then Exit Sub
For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
rngDel.Delete
End Sub

In article ,
"Ron de Bruin" wrote:

Or testing the whole row

Sub DeleteEmptyRows()
'JW
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
Next R
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl


"Tom Ogilvy" wrote in message
...
assume an empty row would have a blank cell in column A, other wise it
would
not.

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
--
Regards,
Tom Ogilvy

"Kaj Pedersen" wrote in message
...
Hi
Is it possible to write a macro that removes empty rows in a
specified
range?
I hope someone can help.
Regards
Kaj Pedersen

--
(ROT13)