Delete blank rows

[asudevils5150]

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]

Here's a macro to put in your personal macro workbook. It deletes all empty rows and columns in the active sheet:

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]

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]

If columns A - E are empty, then the macro I posted should delete them. Are there non-empty cells in those columns?

 

[asudevils5150]

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]

Can anyone else add the this post? The subroutine provided works great, but i added a scenario to it. I love this website!!!

 

[btadams]

Can you post an example of your worksheet using Colo's utility?

 

[btadams]

Maybe this would work (I haven't tested it so run it on a copy of the worksheet):

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

 

[asudevils5150]

IT WORKS!!!! Thanks!!!!