Condense a range to exclude blank rows

Bluzdude

New Member
Joined
Jan 21, 2009
Messages
23
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?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If the valid data is constants...
Code:
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
If I understand what you want, this code will delete all the blank cells in your worksheet...
Code:
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

New Member
Joined
Jan 21, 2009
Messages
23
If I understand what you want, this code will delete all the blank cells in your worksheet...
Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

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

Denis
 
L

Legacy 68668

Guest
try
Code:
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

New Member
Joined
Jan 21, 2009
Messages
23

ADVERTISEMENT

If the valid data is constants...
Code:
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: "".
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,935
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top