Deleting blank cells in a range

[shauntwo]

Hi,

Is there an Excel method, or any other quick and easy way to remove blank cells from a range, either row or column. In my case, I have a sheet of records that randomly contain blank cells, and I'd like to delete the cells and shift all remaining data to the left, so that I have one continuous range of non-null cells for each record.

Thanks for your help!

Shaun

 

[shauntwo]

Sorry - I've should have noted above that in my particular case, the range does NOT concern the entire record; rather, the beginning of my row range begins with the B column.

Thanks again.

Shaun

 

[SkipVought]

hi,

Here's a way for the entire UsedRange. Modify to fit the range you wish to act on...

Sub EmptyCellsShiftToLeft()
    For Each cell In ActiveSheet.UsedRange
        If IsEmpty(cell.Value) Then
           cell.Delete Shift:=xlShiftToLeft
        End If
    Next
End Sub
[\code]
Hope this helps  :-) Skip,
SkipAndMary1017@mindspring.com

 

[bryanbayfield]

Sub no_blanks()
ActiveSheet.UsedRange
With ActiveSheet.UsedRange
    For Each rw In .Rows
        For i = .Columns.Count To 1 Step -1
            If Range(Intersect(rw, .Columns(i)).Address).Value = "" _
                Then: Range(Intersect(rw, .Columns(i)).Address).Delete (xlShiftToLeft)
            Next
        Next
    End With
End Sub

 

[bryanbayfield]

My code can be amended so that column A is ignored by altering the interior loop:

For i = .Columns.Count To 1 Step -1

to:

For i = .Columns.Count To 2 Step -1

 

[shauntwo]

Guys,

Thanks to both of you for your quick replies. With a little practice and time, hopefully I can become as fluent in VBA as the two of you clearly seem to be.

Thanks again.

Shaun

 

[bryanbayfield]

Shaun: the best advice I can give is to get yourself a good book. Practice alone won't get you there - you need a book to guide otherwise you will get frustrated and confused.

The O'Reilly book by Stephen Roman is a fairly good starter - it won't give you the whole picture but will definitely get you going.

 

[shauntwo]

Thanks Bryan - I just might pick up a copy of that. I could use some guidance, especially since I don't have a copy of the Visual Basic help files.

Thanks again.

 

[SkipVought]

I like John Walkenbach's VBA reference books.

Also

http://www.j-walk.com

Skip,
SkipAndMary1017@mindspring.com

 

[shauntwo]

Thanks - I'll put that on my list of books to check out as well. By the way, to both you and Bryan: I decided (maybe foolishly) to use a derivative along the lines of:

Range("A2&quot.CurrentRegion

for my code, which really seemed to speed up the loops. As I've learned, and as you may concur, UsedRange doesn't always include the range one intends - it includes the largest used range that ever was used for that sheet. I've seen some code on here that fixes that, but it doesn't come to mind.

Thanks for both of your input.

 

[bryanbayfield]

First line of my code resets the UsedRange, so there wouldn't be a problem using it. B-)

However, using CurrentRegion is fine, provided you only wanted to clear up the data table that you were currently in, ie if the sheet has more than more region on it, only one region will be cleared up.

Keep on trucking - you will get there.

: