J
juergenkemeter
Hi!
is there a way in VBA to remove all empty rows in a worksheet
'Sheet1'?
Cheers
Juergen
is there a way in VBA to remove all empty rows in a worksheet
'Sheet1'?
Cheers
Juergen
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Norman Jones
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"
Try:
'=============>>
Public Sub Tester()
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
'<<=============
---
Regards,
Norman
"juergenkemeter"
N
Norman Jones
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.Delete
On Error GoTo 0
End Sub
'<<=============
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.Delete
On Error GoTo 0
End Sub
'<<=============
J
juergenkemeter
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 |
+-------------------------------------------------------------------+
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 |
+-------------------------------------------------------------------+
D
Dave Peterson
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
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
N
Norman Jones
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.Delete
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"
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.Delete
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"
N
Norman Jones
Hi Juergen,
Given, however, Dave's intervening and comprehensive response, that should
be unnecessary.
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.
D
Dave Peterson
Missed it by a minute!
Norman said:Hi Juergen,
Given, however, Dave's intervening and comprehensive response, that should
be unnecessary.
J
juergenkemeter
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
Dave, your first code sample does the necessary. Could you shortly
explain how your code works?
Thanks to you both for your help!
Juergen
D
Dave Peterson
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
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
J
juergenkemeter
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
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
N
Norman Jones
Hi Juergen.
Try changing:
to:
If Application.CountA(Cells(iRow, "A").Resize(1, 10)) = 0 Then
---
Regards,
Norman
"juergenkemeter"
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:
to:
If Application.CountA(Cells(iRow, "A").Resize(1, 10)) = 0 Then
---
Regards,
Norman
"juergenkemeter"
J
juergenkemeter
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 |
+-------------------------------------------------------------------+
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 |
+-------------------------------------------------------------------+
N
Norman Jones
Hi Jürgen,
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.
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.
J
juergenkemeter
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
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
N
Norman Jones
Hi Jurgen,
No, That is not correct.
From the immediate window:
?Range("A1").Resize(1,10).Address
$A$1:$J$1
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.