Hi!
is there a way in VBA to remove all empty rows in a worksheet
'Sheet1'?

Cheers
Juergen


--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=499951

Hi Juergen,

Try:

'=============
Public Sub Tester()
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
'<<=============


---
Regards,
Norman


"juergenkemeter"
<juergenkemeter.21fyyy_1136934901.6862@excelforu m-nospam.com wrote in
message news:juergenkemeter.21fyyy_1136934901.6862@excelfo rum-nospam.com...

Hi!
is there a way in VBA to remove all empty rows in a worksheet
'Sheet1'?

Cheers
Juergen


--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile:
http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=499951

Hi Juergen,

Or, better and less telegrammatic:

'=============
Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

On Error Resume Next
SH.Columns(1).SpecialCells(xlBlanks).EntireRow.Del ete
On Error GoTo 0

End Sub
'<<=============

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Juergen,

Try:

'=============
Public Sub Tester()
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
'<<=============


---
Regards,
Norman


"juergenkemeter"
<juergenkemeter.21fyyy_1136934901.6862@excelforu m-nospam.com wrote in
message
news:juergenkemeter.21fyyy_1136934901.6862@excelfo rum-nospam.com...

Hi!
is there a way in VBA to remove all empty rows in a worksheet
'Sheet1'?

Cheers
Juergen


--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile:
http://www.excelforum.com/member.php...o&userid=25248
View this thread:
http://www.excelforum.com/showthread...hreadid=499951

Hi,
I tried

Code:
--------------------

Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

On Error Resume Next
SH.Columns("B:M").SpecialCells(xlBlanks).EntireRow .Delete
On Error GoTo 0

End Sub

--------------------


to delete all rows which have no value in range B to M. Somehow it does
nothing special...
I enclosed an example workbook.

cheers
Juergen


+-------------------------------------------------------------------+
|Filename: DeleteEmptyRow.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4206 |
+-------------------------------------------------------------------+

--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=499951

Norman's code actually looked at one column to determine if the row is empty.

If you can't pick out a column that is always filled in whenever something in
that row is filled in, you could use something like:

Option Explicit
Public Sub Tester2a()
Dim WB As Workbook
Dim SH As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
FirstRow = 1

For iRow = LastRow To FirstRow Step -1
If Application.CountA(.Rows(iRow)) = 0 Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you really only wanted to check columns B:M (ignoring A and N:IV):

Option Explicit
Public Sub Tester2a()
Dim WB As Workbook
Dim SH As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
FirstRow = 1

For iRow = LastRow To FirstRow Step -1
If Application.CountA _
(.Range(.Cells(iRow, "B"), .Cells(iRow, "M"))) = 0 Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub


juergenkemeter wrote:

Hi,
I tried

Code:
--------------------

Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

On Error Resume Next
SH.Columns("B:M").SpecialCells(xlBlanks).EntireRow .Delete
On Error GoTo 0

End Sub

--------------------

to delete all rows which have no value in range B to M. Somehow it does
nothing special...
I enclosed an example workbook.

cheers
Juergen

+-------------------------------------------------------------------+
|Filename: DeleteEmptyRow.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4206 |
+-------------------------------------------------------------------+

--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=499951

--

Dave Peterson

Hi Juergen,

You appear to have moved the goal posts: your original question was to
remove all empty rows.

However, try:

'=============
Public Sub Tester3()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

On Error Resume Next
SH.Columns("B").SpecialCells(xlBlanks).EntireRow.D elete
On Error GoTo 0

End Sub
'<<=============


If, however, column B cannot be used to define empty rows, post back with
more detail.


---
Regards,
Norman


"juergenkemeter"
<juergenkemeter.21g1ip_1136938205.9638@excelforu m-nospam.com wrote in
message news:juergenkemeter.21g1ip_1136938205.9638@excelfo rum-nospam.com...

Hi,
I tried

Code:
--------------------

Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

On Error Resume Next
SH.Columns("B:M").SpecialCells(xlBlanks).EntireRow .Delete
On Error GoTo 0

End Sub

--------------------


to delete all rows which have no value in range B to M. Somehow it does
nothing special...
I enclosed an example workbook.

cheers
Juergen


+-------------------------------------------------------------------+
|Filename: DeleteEmptyRow.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4206 |
+-------------------------------------------------------------------+

--
juergenkemeter
------------------------------------------------------------------------
juergenkemeter's Profile:
http://www.excelforum.com/member.php...o&userid=25248
View this thread: http://www.excelforum.com/showthread...hreadid=499951

Hi Juergen,

If, however, column B cannot be used to define empty rows, post back with
more detail.

Given, however, Dave's intervening and comprehensive response, that should
be unnecessary.

---
Regards,
Norman