VBA - remove all empty rows in a worksheet
 

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

VBA - remove all empty rows in a worksheet
 

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

VBA - remove all empty rows in a worksheet
 

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

VBA - remove all empty rows in a worksheet
 

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

VBA - remove all empty rows in a worksheet
 

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

VBA - remove all empty rows in a worksheet
 

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

VBA - remove all empty rows in a worksheet
 

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

VBA - remove all empty rows in a worksheet
 

Missed it by a minute!

Norman Jones wrote:

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

--

Dave Peterson

VBA - remove all empty rows in a worksheet
 

Hi!
Dave, your first code sample does the necessary. Could you shortly
explain how your code works?

Thanks to you both for your help!
Juergen


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

VBA - remove all empty rows in a worksheet
 

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 it up to point at the correct workbook/worksheet
Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
'lastrow is the same as the row number for the cell you go
'to when you hit ctrl-end manually.
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

'just a stopping point.
'if you had a bunch of headers (some empty rows), you
'could ignore them by changing this to a larger number
FirstRow = 1

'start at the lastrow and go up the rows (step -1 is up)
For iRow = LastRow To FirstRow Step -1
'if you see anything (formulas or values in that row)
'then =counta() will be 0
If Application.CountA(.Rows(iRow)) = 0 Then
'but if it no cells are filled in, then
'delete that row
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

juergenkemeter wrote:

Hi!
Dave, your first code sample does the necessary. Could you shortly
explain how your code works?

Thanks to you both for your help!
Juergen

--
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

VBA - remove all empty rows in a worksheet
 

Hi,
can I specify a certain range condition for deleting a row, for Dave's
code?

e.g.

If there are no values in Range(Columns A : J), then
delete row


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

VBA - remove all empty rows in a worksheet
 

Hi Juergen.

can I specify a certain range condition for deleting a row, for Dave's
code?

e.g.

If there are no values in Range(Columns A : J), then
delete row

Try changing:

If Application.CountA(.Rows(iRow)) = 0 Then

to:

If Application.CountA(Cells(iRow, "A").Resize(1, 10)) = 0 Then


---
Regards,
Norman



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

Hi,
can I specify a certain range condition for deleting a row, for Dave's
code?

e.g.

If there are no values in Range(Columns A : J), then
delete row


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

VBA - remove all empty rows in a worksheet
 

Hi!
I altered the line, but nothing happens with the lines.
I enclosed an example file - principally, I want to delete all rows in
the Sheet, except rows 1,3,4,5,7,8. All these rows contain values in
column range A to J.

Cheers
Jürgen


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

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

VBA - remove all empty rows in a worksheet
 

Hi Jürgen,

I altered the line, but nothing happens with the lines.
I enclosed an example file - principally, I want to delete all rows in
the Sheet, except rows 1,3,4,5,7,8. All these rows contain values in
column range A to J.

I have not opened your attachment but I have reviewed my test book. In my
tests any rows on Sheet1 of the active workbook are deleted if, and only if,
columns A:J are blank. This is as expected and in accordance with the code's
logic.

Can I suggest, therefore, that you retry the suggested code, ensuring that
the expected worksheet is active.

Should you wish me to send you my test book, please provide an appropriately
disguised email address.


---
Regards,
Norman

VBA - remove all empty rows in a worksheet
 

Hi Norman,
thanks, it works. I used the line
If Application.CountA(Cells(iRow, "A").Resize(1, 9)) = 0
Then

than means 1,9 and not 1,10 (10 would mean that it also looks into
column K).

Thanks for your help.

Cheers
Jürgen


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

VBA - remove all empty rows in a worksheet
 

Hi Jurgen,

Hi Norman,
thanks, it works. I used the line
If Application.CountA(Cells(iRow, "A").Resize(1, 9)) = 0
Then

than means 1,9 and not 1,10 (10 would mean that it also looks into
column K).

No, That is not correct.

From the immediate window:

?Range("A1").Resize(1,10).Address
$A$1:$J$1

---
Regards,
Norman