Delete blank rows

asudevils5150

New Member
Joined
Feb 19, 2002
Messages
49
Also, how can I delete blank rows in a spreadsheet with just a few commands. My data cells have blank rows all over the spreadsheet. I want to make sure I delete those rows with no data, and not delete something that might be in cell AH1 or maybe CC columns.

Thanks
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
Here's a macro to put in your personal macro workbook. It deletes all empty rows and columns in the active sheet:

Code:
Sub DeleteEmptyRows()
Dim LastRow As Long, LastColumn As Long, r As Long


    intResponse = MsgBox("This will delete all empty rows and columns in this worksheet.", vbOKCancel, "Delete Empty Rows/Columns")
    If intResponse = vbOK Then
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
    
            LastRow = ActiveSheet.UsedRange.Rows.count
            LastColumn = ActiveSheet.UsedRange.Columns.count
            Application.ScreenUpdating = False
            For r = LastRow To 1 Step -1
                If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
            Next r
        
            For r = LastColumn To 1 Step -1
                If Application.CountA(Columns(r)) = 0 Then Columns(r).Delete
            Next r
        
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With

    End If
    Application.ScreenUpdating = True

End Sub
 

asudevils5150

New Member
Joined
Feb 19, 2002
Messages
49
That subroutine works great. BUT, I still get blank cells from columns A-E. In other words, how can i shove all the data to start in Column A, regardless of where the data starts at (Column D, Z, AE, etc.) Thanks again
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
If columns A - E are empty, then the macro I posted should delete them. Are there non-empty cells in those columns?
 

asudevils5150

New Member
Joined
Feb 19, 2002
Messages
49
The empty rows are deleted. There is still data from let's say column N that may have moved to Column G only. It appears the data staggered. Originally the data cells were staggered so that there was data in A1:E6, then G9:i19, then N25:P57, etc. The subroutine eliminated the blank rows and the blank columns, so the macro you provided for what I asked for was correct, but now i need something else so that the data in columns G9:i19 that I think were moved to F7:h17 goes to now A7:C17. Basically, the remaining blank cells that precede data in columns other than A are shoved back into column A. Thanks again
 

asudevils5150

New Member
Joined
Feb 19, 2002
Messages
49
Can anyone else add the this post? The subroutine provided works great, but i added a scenario to it. I love this website!!!
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
Can you post an example of your worksheet using Colo's utility?
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
Maybe this would work (I haven't tested it so run it on a copy of the worksheet):

Code:
Sub DeleteEmptyRows() 
Dim LastRow As Long, LastColumn As Long, r As Long 


    intResponse = MsgBox("This will delete all empty rows and columns in this worksheet.", vbOKCancel, "Delete Empty Rows/Columns") 
    If intResponse = vbOK Then 
        With Application 
            .Calculation = xlCalculationManual 
            .ScreenUpdating = False 
    
            LastRow = ActiveSheet.UsedRange.Rows.count 
            LastColumn = ActiveSheet.UsedRange.Columns.count 
            Application.ScreenUpdating = False 
            For r = LastRow To 1 Step -1 
                If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete 
            Next r 
        
            For r = LastColumn To 1 Step -1 
                If Application.CountA(Columns(r)) = 0 Then Columns(r).Delete 
            Next r 
        
            ActiveSheet.Usedrange.Specialcells(xlBlanks).Delete shift:=xlToLeft

            .Calculation = xlCalculationAutomatic 
            .ScreenUpdating = True 
        End With 

    End If 
    Application.ScreenUpdating = True 

End Sub
 

Forum statistics

Threads
1,077,782
Messages
5,336,284
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top