Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows
On Sat, 27 Aug 2011 13:57:51 +0300, "Rafeek" wrote:
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 ======================== |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows
Thank you, Ron.
"Ron Rosenfeld" wrote in message ... On Sat, 27 Aug 2011 13:57:51 +0300, "Rafeek" wrote: 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 ======================== |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows
Thank you, Jim.
"Jim Cone" wrote in message ... 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows
Without sorting..............select column A then
EditGotoSpecialBlanks EditDeleteEntire Row. Gord Dibben Microsoft Excel MVP On Sat, 27 Aug 2011 17:07:33 +0300, "Rafeek" wrote: Thank you, Jim. "Jim Cone" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help Deleting Blank Rows | Excel Worksheet Functions | |||
deleting blank rows | New Users to Excel | |||
Deleting Blank Rows. | Excel Discussion (Misc queries) | |||
Deleting blank rows | Excel Worksheet Functions | |||
Deleting Blank Rows | New Users to Excel |