Delete blank rows using the following macro?

[DavidJ726]

I've been searching on how to remove blank rows and have come up with a
couple of solutions. Back on August 21, JulieD posted the following answer
to the thread, Delete all blank rows in sheet?

Highlight a column....Edit>Go To>Special>Blanks>OK. Delete>Entire Row. it
will work, but you must highlight a COLUMN not a ROW

This process works fine for a manual process, but I was hoping for something
more automated.

On 09/23, Frank posted this response to the thread Removing blank rows?
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

This seemed like what I was after, although to be honest I'm not
understanding everything in the macro. I thought I inserted the macro into
my personal.xls, but when I run it on a test worksheet by selecting
Tools/Macro/Macros, I don't get any results. BTW... it is showing up as:
personal.xls!DeleteRows. This seems to be normal as another macro I have in
my personal.xls shows up (and works) in the same manner.

So I guess my question is, is there something "special" that I can't
remember doing, or don't know, that needs to be done to make sure the macro
is working properly? Or maybe the following macro really isn't what I'm
looking for?

Basically, all I'm doing is deleteing blank rows. I have a digital
thermometer with probes that are reading the temperatures in a roasting
oven. The meter has a RS-232 port and I can export the recorded data to a
*.xls worksheet. However there are blank rows in the worksheet I want to
delete before I use or export the data in another worsheet. There are
several columns that have data such as the date, time, type of probe,
temperature, etc... and all I want/need to do is delete the blank rows.

The macro looks like this, other than changing the 1st line as indicated
below, I made no other change.

Any help on what I'm doing wrong is appreciated,

David...

This was the original 1st line that I modified. Basically removed public &
changed the macro name
Public Sub DeleteBlankRows()

Sub DeleteRows()

' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Dave Peterson]

That code was written to delete rows that have duplicated values in a column.

From the top of that routine you posted:

' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

If you can pick a column that is only blank when the row is completely empty,
you could use a macro like:

Option Explicit
Sub deleteBlanks1()
On Error Resume Next
ActiveSheet.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub



If you can't depend on any individual column, you could use a macro like this:

Option Explicit
Sub deleteBlanks2()
Application.ScreenUpdating = False
Dim iRow As Long
Dim delRng As Range
With ActiveSheet
For iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If Application.CountA(.Rows(iRow)) = 0 Then
If delRng Is Nothing Then
Set delRng = .Cells(iRow, "A")
Else
Set delRng = Union(.Cells(iRow, "A"), delRng)
End If
End If
Next iRow
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Delete
End If

Application.ScreenUpdating = True
End Sub

 

[DavidJ726]

Thanks Dave,

Boy do I feel like an idiot copying the wrong macro, right page wrong
macro... but I'm still having problems and don't know why, this is what
I've done:

Opened Excel & unhide personal.xls (Personal.xls opens as a hidden
worksheet)
Go to Tools/Macro/Visual Basic Editor
Go to Tools/Macros and in the name field I put deleteBlanks1
Click on create

(I assume I could call it what I want as long as it's reflected in the
macro...)

Personal.xls - Module3 (Code) opens up. Theres already a line that says
Option Explicit, than a horizontal (divider?) line below it, then Sub
deleteBlanks1 (), a blank line, then End Sub.

I copy and paste lines 3, 4 & 5 from your macro before the End Sub portion
above.
Then I close Module 3, then close the editor.

Then I open up a new blank workbook and put data (numbers 1 - 10) only in
cells C1, C3, C5, etc.... C20
Then I Select Column A and run the macro. All data is deleted
Then I Select Column C and run the macro. All data is deleted
I'll re-enter the numbers and select cell A1 and run the macro, all data is
deleted.
I'll re-enter the numbers and select cell A2 and run the macro, all data is
deleted.
I'll re-enter the numbers and select Row 1 and run the macro, all data is
deleted.
I'll re-enter the numbers and select Row 2 and run the macro, all data is
deleted.

I have to assume I only need to select an empty column before running the
macro, right? any column? I went through all those other actions only
because I wasn't sure if what I thought I needed to do was correct.

Then I closed Excel, making sure that personal.xls was saved. The I opened
Excel again, ran through the same scenerios above (except for creating the
macro) and the data is always deleted. So it appears that I'm deleting rows
with data in it, not deleting blank rows.

What is it I'm doing wrong ?

Thanks,

David...

 

[Gord Dibben]

David

ActiveSheet.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Note the Range("a:a")

You don't select anything. The selection is made for you in the code.

This macro works only on column A. It deletes all rows in which there are
blanks in Column A.

Entering your data in Column C and nothing in Column A ensures that all rows
will be deleted.

If you wish it to run on Column C, change the a:a to c:c

Gord Dibben Excel MVP

Thanks Dave,

Boy do I feel like an idiot copying the wrong macro, right page wrong
macro... but I'm still having problems and don't know why, this is what
I've done:

Opened Excel & unhide personal.xls (Personal.xls opens as a hidden
worksheet)
Go to Tools/Macro/Visual Basic Editor
Go to Tools/Macros and in the name field I put deleteBlanks1
Click on create

(I assume I could call it what I want as long as it's reflected in the
macro...)

Personal.xls - Module3 (Code) opens up. Theres already a line that says
Option Explicit, than a horizontal (divider?) line below it, then Sub
deleteBlanks1 (), a blank line, then End Sub.

I copy and paste lines 3, 4 & 5 from your macro before the End Sub portion
above.
Then I close Module 3, then close the editor.

Then I open up a new blank workbook and put data (numbers 1 - 10) only in
cells C1, C3, C5, etc.... C20
Then I Select Column A and run the macro. All data is deleted
Then I Select Column C and run the macro. All data is deleted
I'll re-enter the numbers and select cell A1 and run the macro, all data is
deleted.
I'll re-enter the numbers and select cell A2 and run the macro, all data is
deleted.
I'll re-enter the numbers and select Row 1 and run the macro, all data is
deleted.
I'll re-enter the numbers and select Row 2 and run the macro, all data is
deleted.

I have to assume I only need to select an empty column before running the
macro, right? any column? I went through all those other actions only
because I wasn't sure if what I thought I needed to do was correct.

Then I closed Excel, making sure that personal.xls was saved. The I opened
Excel again, ran through the same scenerios above (except for creating the
macro) and the data is always deleted. So it appears that I'm deleting rows
with data in it, not deleting blank rows.

What is it I'm doing wrong ?

Thanks,

David...

 

[DavidJ726]

Thanks Gord,

Now I understand, and it works like it's meant to...

David...

 

[Gord Dibben]

Thanks for the feedback.

Always nice to know what works or doesn't work. Also google gets to archive
the feedback along with the original problem/answer.

Gord Dibben Excel MVP