Deleting Blank Rows

[Guest]

The problem is there is a blank
rows between blocks of data (37 blocks of data) I want to be able to delete
the blank rows... the blank rows can vary each month...

thanks

 

[Norman Jones]

Hi Reeni,

Assuming that the blank rows are determined by blank cells in column A, try
the following macro:

'=============>>
Public Sub Tester1()
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks). _
EntireRow.Delete
End Sub
'<<=============

If you are new to macros, you may wish to visit David McRitchie's 'Getting
Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Norman Jones]

Hi Reeni,

If you want to perform the operation manually, try:

Select columnA
F5 | Special | select the 'Blanks' option | OK | Right-click | Delete |
Entire row | OK

 

[Guest]

There is a feature in Jim Cone's fine commercial Add-in called XL Companion
that does this nicely............I use it regularly.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Two options; each makes an assumption

Option 1: Assuming there is some semblance of order to the data; simply
sort the data.............blanks will shift to the bottom.

Option 2: Assuming one column of data always has data and as such the cell
in that column is blank is ONLY the blank row........... consider use of the
"Filter" options of EXCELand do the following:

Turn on Filtering by selecting from the Task Bar Data, Filters, Autofilters

This will activate what appears as a dropdown in each of the column header
cells (Row 1)

In the column identified above that contains data in all but the blank rows;
select the "Blanks" from the bottom of the dropdown displayed

Hit enter

Only the "blank" rows will be displayed

Highlight/select the "blank" rows

Hit "delete"

Then go to the header row and reselect the dropdown from row 1 of the column
and select ALL

Hit enter

Your data now appears without the blanks (they are gone).