Condense a range to exclude blank rows

[Bluzdude]

I have a large range of cells. Some of the rows within this range contain all blank cells. I need a macro that will identify the blank rows and move the next row that contains values up to the last row that contained values, thus, condensing or compressing the range to include only rows that contain values. Each row will have a column that, when blank, indicates that the rest of the cells on that row are also blank.

I don't want to delete entire rows from the worksheet because that would remove data in other locations on the rows.

I assume the macro would have to do something like stepping through each row in the range and then do a "select" and "move" for the rows below the last row containing values and continue to the bottom of the range.

Is this possible?

 

[SydneyGeek]

If the valid data is constants...

For Each c In Range("B2:B6000").SpecialCells(xlCellTypeConstants)
  '...do stuff
Next c

If it's formulas, and assuming that the "blank" cells are truly blank, you can use xlCellTypeFormulas instead.

Denis

 

[Datsmart]

If I understand what you want, this code will delete all the blank cells in your worksheet...

With ActiveSheet
    Cells.SpecialCells(xlCellTypeBlanks).Delete
End With

Of course, your data will be aligned differently dependant on the number blank cells in each column.

 

[Bluzdude]

If I understand what you want, this code will delete all the blank cells in your worksheet...

With ActiveSheet
    Cells.SpecialCells(xlCellTypeBlanks).Delete
End With

Of course, your data will be aligned differently dependant on the number blank cells in each column.

Thanks, it appears that this code would "delete" all blank cells in the entire worksheet. As I stated, I only want to delete blank cells that are in rows where all the other cells in that particular row are also blank.

Your code, I assume, would delete all blank cells in the entire sheet.

 

[SydneyGeek]

If you only want to delete blanks in A:J you could use

Range("A:J").SpecialCells(xlCellTypeBlanks).Delete

Denis

 

[Legacy 68668]

try

Sub test()
Dim rng As Ragne, i As Long
Set rng = Application.InputBox("Select range", type:=8)
If rng Is Nothing Then Exit Sub
With rng
    For i = .Rows.Count To 1 Step -1
        If Application.CountBlank(.Rows(i).Cells) = .Columns.Count Then _
            .Rows(i).Delete xlShiftUp
    Next
End With
End Sub

 

[Bluzdude]

If the valid data is constants...

For Each c In Range("B2:B6000").SpecialCells(xlCellTypeConstants)
  '...do stuff
Next c

If it's formulas, and assuming that the "blank" cells are truly blank, you can use xlCellTypeFormulas instead.

Denis

The actual values have been copied from a different area, but on the same rows, and then a "paste-special/values" action was performed. The values in the blank cells would be the result of pasting the value from a cell where the formula resulted in this: "".

 

[Datsmart]

There is a rather extensive answer here:
http://www.cpearson.com/excel/deleting.htm

There is lots of error trapping and good commenting in his code.

 

[SydneyGeek]

The actual values have been copied from a different area, but on the same rows, and then a "paste-special/values" action was performed. The values in the blank cells would be the result of pasting the value from a cell where the formula resulted in this: "".

That being the case, you can clear all of the "" entries first by doing something like

Dim c as Range
For Each c In Range("A:J").SpecialCells(xlCellTypeConstants)
  If c.Value="" Then c.ClearContents
Next c
'... then do the deletion step here, using SpecialCells to select blanks

Denis

 

[Bluzdude]

There is a rather extensive answer here:
http://www.cpearson.com/excel/deleting.htm

There is lots of error trapping and good commenting in his code.

Again, it appears that the code appearing in the link you posted will delete entire rows in the worksheet. This is unacceptable. There are values and formulas on those rows, but in different columnar locations, that must not be deleted.