Deleting blank rows

[Rafeek]

In an accounting software generated reports, there are alternate blank rows
between rows that contains data.

Row1: Data
Row2: Blank
Row3: Data
Row4: Blank
Row5: Data
...................
...................

I need the blank rows removed for exporting this data into MS Access.
Presently I am doing this by deleting one by one and would love to learn any
short cut that is available in excel. I am using Excel 2007.

Thanks in advance.
rafeek.

 

[Ron Rosenfeld]

In an accounting software generated reports, there are alternate blank rows
between rows that contains data.

Row1: Data
Row2: Blank
Row3: Data
Row4: Blank
Row5: Data
..................
..................

I need the blank rows removed for exporting this data into MS Access.
Presently I am doing this by deleting one by one and would love to learn any
short cut that is available in excel. I am using Excel 2007.

Thanks in advance.
rafeek.

For each row in a selected range, this macro will check the first cell in the row. If that cell is empty, it will delete the entire row. Empty means that the cell is truly empty. So if there is a <space> or a null string or a formula that is returning a blank, these will not be deleted. This behavior can be changed, if necessary, so as to delete anything that appears blank.

Also, the selection of the range could be automated in a variety of ways.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range to process. Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

======================
Option Explicit
Sub DeleteBlankRows()
Dim rg As Range
Dim i As Long
Set rg = Selection
For i = rg.Rows.Count To 1 Step -1
If IsEmpty(rg(i, 1)) Then
rg(i, 1).EntireRow.Delete
End If
Next i
End Sub
========================

 

[Jim Cone]

You can also just sort the data.
Blank cells sort to the bottom.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
editorial review of special sort add-in (30 ways to sort)






"Rafeek" <[email protected]>
wrote in message
news:[email protected]...

 

[Rafeek]

Thank you, Ron.

For each row in a selected range, this macro will check the first cell in
the row. If that cell is empty, it will delete the entire row. Empty
means that the cell is truly empty. So if there is a <space> or a null
string or a formula that is returning a blank, these will not be deleted.
This behavior can be changed, if necessary, so as to delete anything that
appears blank.

Also, the selection of the range could be automated in a variety of ways.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range to process. Then <alt-F8>
opens the macro dialog box. Select the macro by name, and <RUN>.

======================
Option Explicit
Sub DeleteBlankRows()
Dim rg As Range
Dim i As Long
Set rg = Selection
For i = rg.Rows.Count To 1 Step -1
If IsEmpty(rg(i, 1)) Then
rg(i, 1).EntireRow.Delete
End If
Next i
End Sub
========================

 

[Rafeek]

Thank you, Jim.

You can also just sort the data.
Blank cells sort to the bottom.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
editorial review of special sort add-in (30 ways to sort)






"Rafeek" <[email protected]>
wrote in message

 

[Gord]

Without sorting..............select column A then
Edit>Goto>Special>Blanks

Edit>Delete>Entire Row.


Gord Dibben Microsoft Excel MVP